找到所有具有列名的表名吗？

如何获取给定列名存在的所有表名？我需要 sql 服务器中带有“ Like”的名称。例如:-

  select TableNames where columnname like '%MyColumn%'

请帮帮我

sql-server

417951 次浏览

小开

试试这样: 用于 SQLSERVER2008 +

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 '%MyColumnaName%'

或者

SELECT COLUMN_NAME, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%MyName%'

或者像这样的东西:

SELECT name
FROM sys.tables
WHERE OBJECT_ID IN ( SELECT id
FROM syscolumns
WHERE name like '%COlName%' )

小开

你可以这样做:

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 '%MyColumn%'
ORDER BY schema_name, table_name;

参考文献:

SQLSERVER-从数据库的所有表中查找列的查询

小开

最佳答案

请尝试下面的查询

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 '%MyCol%';