如何从含5万余张表的数据库中筛选不带末尾十六进制后缀的表？

核心逻辑：先通过正则匹配你给出的十六进制后缀格式_dft<7位十六进制>_<4位十六进制>_<4位十六进制>_<4位十六进制>_<12位十六进制>，反向过滤即可得到所有不带该后缀的表名，不同数据库的实现语句如下：

MySQL #

SELECT table_name
FROM information_schema.tables
WHERE table_schema = '替换为你的实际数据库名'
AND table_name NOT REGEXP '_dft[0-9a-f]{7}_([0-9a-f]{4}_){3}[0-9a-f]{12}$';

默认不区分大小写，若需严格匹配大小写可将REGEXP替换为REGEXP BINARY

PostgreSQL #

SELECT table_name
FROM information_schema.tables
WHERE table_schema = '替换为你的实际schema名，默认public'
AND table_name !~* '_dft[0-9a-f]{7}_([0-9a-f]{4}_){3}[0-9a-f]{12}$';

!~*代表不区分大小写的反向正则匹配，需严格区分大小写可替换为!~

SQL Server #

SELECT name AS table_name
FROM sys.tables
WHERE PATINDEX('%[_]dft[0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F]_[0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F]_[0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F]_[0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F]_[0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F]', name) = 0;

内容的提问来源于stack exchange，提问作者Somethingwhatever