SELECT table_schema + '.' + table_name,
       column_name
FROM   [yourdatabase].information_schema.columns
WHERE  column_name LIKE '%myName%' 

这将为您提供需要查找的列的表名。

像oracle一样，您可以通过以下方式查找表和列：

select table_name, column_name
from user_tab_columns 
where column_name 
like '%myname%';

在MS SQL Server数据库中，使用此查询获取包含输入文本的表和相应的列名：

SELECT t.name AS tableName, c.name AS columnName 
FROM sys.tables as t 
INNER JOIN sys.columns AS c ON t.object_id=c.object_id 
WHERE c.name LIKE '%<your_search_string>%'

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

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  [TABLE_NAME] ,
        [INFORMATION_SCHEMA].COLUMNS.COLUMN_NAME
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME LIKE '%NAME%' ;