DECLARE @columnName as varchar(100)
SET @columnName = 'ColumnName'

SELECT t.name AS Table, c.name AS Column,
ty.name AS Type, c.max_length AS Length
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id
WHERE c.name LIKE @columnName
ORDER BY t.name, c.name

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;

我们还可以使用以下语法：-

select * from INFORMATION_SCHEMA.COLUMNS 
where COLUMN_NAME like '%clientid%' 
order by TABLE_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 'DBName' AS DBName,
       column_name,
       table_name,
       table_schema
FROM   dbname.information_schema.columns
WHERE  column_name LIKE '%YourColumnName%'
       AND table_schema IN ( 'YourSchemaName' ) 

您还可以在多个数据库中找到同一列。

SELECT 'DBName1' AS DB,
       column_name,
       table_name,
       table_schema
FROM   dbname1.information_schema.columns
WHERE  column_name LIKE '%YourColumnName%'
UNION
SELECT 'DBName2' AS DB,
       column_name,
       table_name,
       table_schema
FROM   dbname2.information_schema.columns
WHERE  column_name LIKE '%YourColumnName%' 

要获取完整信息：列名、表名以及表的架构。。

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