搜索表：

SELECT      c.name  AS 'ColumnName'
            ,t.name AS 'TableName'
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       c.name LIKE '%MyName%'
ORDER BY    TableName
            ,ColumnName;

搜索表和视图：

SELECT      COLUMN_NAME AS 'ColumnName'
            ,TABLE_NAME AS  'TableName'
FROM        INFORMATION_SCHEMA.COLUMNS
WHERE       COLUMN_NAME LIKE '%MyName%'
ORDER BY    TableName
            ,ColumnName;

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

搜索表：

SELECT      c.name  AS 'ColumnName'
            ,t.name AS 'TableName'
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       c.name LIKE '%MyName%'
ORDER BY    TableName
            ,ColumnName;

搜索表和视图：

SELECT      COLUMN_NAME AS 'ColumnName'
            ,TABLE_NAME AS  'TableName'
FROM        INFORMATION_SCHEMA.COLUMNS
WHERE       COLUMN_NAME LIKE '%MyName%'
ORDER BY    TableName
            ,ColumnName;

这是你问题的答案

SELECT c.name AS ColumnName, t.name AS TableName
FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%myName%';

如果你更喜欢第三方工具，有很多选择，比如：

ApexSQL搜索SSMS工具包红门工具。

如果您的数据库包含加密对象（视图、过程、函数），则这些对象非常有用，因为您无法使用系统表轻松搜索这些对象。

用于显示具有指定列名的所有表的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'