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博客

这应该是有效的：

SELECT name 
FROM sysobjects 
WHERE id IN ( SELECT id 
              FROM syscolumns 
              WHERE name like '%column_name%' )

您可以尝试以下查询：

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

用于显示具有指定列名的所有表的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 * from INFORMATION_SCHEMA.COLUMNS 
where COLUMN_NAME like '%clientid%' 
order by TABLE_NAME

以下查询将为您提供字段名为“%myName”的数据库的确切表名。

SELECT distinct(TABLE_NAME)
  FROM INFORMATION_SCHEMA.COLUMNS    
 WHERE COLUMN_NAME LIKE '%myName%'