是否可以查询包含以下列的表名

LIKE '%myName%'

?


当前回答

我刚试过,这个效果很好

USE YourDatabseName
GO
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 '%YourColumnName%'
ORDER BY schema_name, table_name;

只将YourDatabaseName更改为数据库,将YourcolumnName更改为您要查找的列名,其余的保持原样。

希望这有所帮助

其他回答

USE AdventureWorks

GO

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

来自Pinal Sir博客

在MS SQL中,可以编写以下行来检查特定表的列名:

sp_help your_tablename

或者,您可以先在查询窗口中选择表名(突出显示模式和表名),然后按下下面的组合键:

Alt + F1
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 '%Label%'
ORDER BY schema_name, table_name;

我不知道为什么很多人建议使用sys.columns加入sys.table。

您可以使用以下代码:

SELECT Object_name(object_id) AS TableName,
       *
FROM   sys.columns
WHERE  NAME LIKE '%MyName%' 

or

如果还需要架构名称:

SELECT *
FROM   information_schema.columns
WHERE  column_name LIKE '%MyName%' 

这个简单的查询对我有用。

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = 'schemaName'
AND column_name like '%model%';