SQL 我想显示所有具有指定列名的表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4197657/
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
I want to show all tables that have specified column name
提问by l--''''''---------''''''''''''
How can I get a list of all the tables that have a specific column name?
如何获取具有特定列名的所有表的列表？
回答by Evidica
Pretty simple on a per database level
在每个数据库级别上非常简单
Use DatabaseName
Select * From INFORMATION_SCHEMA.COLUMNS Where column_name = 'ColName'
回答by RedFilter
select table_name
from information_schema.columns
where COLUMN_NAME = 'MyColumn'
回答by David
You can use the information schema views:
您可以使用信息架构视图：
SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME
FROM Information_Schema.Columns
WHERE COLUMN_NAME = 'ID'
Here's the MSDN reference for the "Columns" view: http://msdn.microsoft.com/en-us/library/ms188348.aspx
这是“列”视图的 MSDN 参考：http: //msdn.microsoft.com/en-us/library/ms188348.aspx
回答by avDev
If you're trying to query an Oracle database, you might want to use
如果您尝试查询 Oracle 数据库，您可能需要使用
select owner, table_name
from all_tab_columns
where column_name = 'ColName';
回答by SKocheta
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name,*
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%YOUR_COLUMN%'
ORDER BY schema_name, table_name;
回答by OneSimpleGeek
SELECT T.TABLE_NAME, C.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS C
INNER JOIN INFORMATION_SCHEMA.TABLES T ON T.TABLE_NAME = C.TABLE_NAME
WHERE TABLE_TYPE = 'BASE TABLE'
AND COLUMN_NAME = 'ColName'
This returns tables only and ignores views for anyone who is interested!
这仅返回表并忽略任何感兴趣的人的视图！
回答by Er Ketan Vavadiya
--get tables that contains selected columnName
--获取包含所选列名的表
SELECT c.name AS ColName, t.name AS TableName
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%batchno%'
its worked...
它的工作...
回答by Jon Onstott
You can find what you're looking for in the information schema: SQL Server 2005 System Tables and Views I think you need SQL Server 2005 or higher to use the approach described in this article, but a similar method can be used for earlier versions.
您可以在信息架构中找到您要查找的内容：SQL Server 2005 系统表和视图 我认为您需要 SQL Server 2005 或更高版本才能使用本文中描述的方法，但类似的方法可用于早期版本。
