它将返回table_name、schema_name、column_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 '%colName%'
ORDER BY schema_name,
table_name;

在MS SQL中，可以编写以下行来检查特定表的列名：

sp_help your_tablename

或者，您可以先在查询窗口中选择表名（突出显示模式和表名），然后按下下面的组合键：

Alt + F1

我想要一些不会让我眼睛流血的桌子和视图。

查询

SELECT
    t.TABLE_TYPE AS [Type],
    c.TABLE_NAME AS [Object],
    c.COLUMN_NAME AS [Column]
FROM
    INFORMATION_SCHEMA.COLUMNS AS c
    LEFT JOIN INFORMATION_SCHEMA.TABLES AS t ON
        t.TABLE_CATALOG = c.TABLE_CATALOG AND 
        t.TABLE_SCHEMA = c.TABLE_SCHEMA AND
        t.TABLE_NAME = c.TABLE_NAME
WHERE
    c.COLUMN_NAME LIKE '%myName%'
ORDER BY
    [Type],
    [Object],
    [Column]

后果

Type        Object   Column
----------------------------
BASE TABLE  Table1   myName1
BASE TABLE  Table2   myName2
VIEW        View1    myName1
VIEW        View2    myName2

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 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;

您可以通过column_name过滤器从INFORMATION_SCHEMA.COLUMNS中找到它

Select DISTINCT TABLE_NAME as TableName,COLUMN_NAME as ColumnName
     From INFORMATION_SCHEMA.COLUMNS Where column_name like '%myname%'