SQL 查找名称以特定后缀结尾的所有表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21013170/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find all tables whose name ends with a certain suffix
提问by Shree
I have thousand of tables in database. Some names end with _History.
我在数据库中有数千个表。一些名称以_History.
For example :
例如 ：
abc_History
bcd_History
123_History
How do I find all tables which name is end with _History.
如何查找名称以_History.
Some thing like:
就像是：
SELECT
table_name
FROM sys.tables WHERE table_name LIKE '_History%'
And
和
error : Invalid column name 'table_name'.
回答by Saharsh Shah
Try this:
尝试这个：
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_NAME LIKE '%_History'
OR
或者
SELECT name
FROM sys.tables
WHERE name LIKE '%_History'
回答by Meloman
Thanks to @Saharsh-shah.
感谢@Saharsh-shah。
To get table name with all column namesin the same query, use this :
要在同一查询中获取所有列名的表名，请使用：
SELECT
`TABLE_NAME`,
group_concat(`COLUMN_NAME` separator ',') AS `COLUMNS`
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `TABLE_NAME` IN(
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.tables
WHERE `TABLE_NAME` LIKE '%_History'
)
GROUP BY `TABLE_NAME`
You can push it in multidimensional PHP array very simply with the following :
您可以使用以下命令非常简单地将其推送到多维 PHP 数组中：
$tables['columns'] = explode(',', $tables['columns']);
Hope it can help some.
希望它可以帮助一些。
