这是你问题的答案

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

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

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

它将返回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;

我不知道为什么很多人建议使用sys.columns加入sys.table。

您可以使用以下代码：

SELECT Object_name(object_id) AS TableName,
       *
FROM   sys.columns
WHERE  NAME LIKE '%MyName%' 

or

如果还需要架构名称：

SELECT *
FROM   information_schema.columns
WHERE  column_name LIKE '%MyName%' 

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

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

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

查询

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