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

LIKE '%myName%'

?


当前回答

Create table #yourcolumndetails(
DBaseName varchar(100), 
TableSchema varchar(50), 
TableName varchar(100),
ColumnName varchar(100), 
DataType varchar(100), 
CharMaxLength varchar(100))

EXEC sp_MSForEachDB @command1='USE [?];
    INSERT INTO #yourcolumndetails SELECT
    Table_Catalog
    ,Table_Schema
    ,Table_Name
    ,Column_Name
    ,Data_Type
    ,Character_Maximum_Length
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME like ''origin'''

select * from #yourcolumndetails
Drop table #yourcolumndetails

其他回答

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 COLUMN_NAME, TABLE_NAME, TABLE_SCHEMA
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE '%col_Name%'

用于显示具有指定列名的所有表的SQL查询:

SELECT SCHEMA_NAME(schema_id) + '.' + t.name AS 'Table Name'
  FROM sys.tables t
 INNER JOIN sys.columns c ON c.object_id = t.object_id
 WHERE c.name like '%ColumnName%'
 ORDER BY 'Table Name'

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

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