希望这不是一个重复的答案，但我想做的是在sql语句中生成一个sql语句，这将允许我搜索我要查找的值（而不仅仅是具有这些字段名称的表（因为通常我需要删除与我要查找列名称的id相关的任何信息）：

  SELECT  'Select * from ' + t.name + ' where ' + c.name + ' = 148' AS SQLToRun
  FROM sys.columns c, c.name as ColName, t.name as TableName
  JOIN sys.tables t 
     ON c.object_id = t.object_id
  WHERE c.name LIKE '%ProjectID%'

然后我可以复制并粘贴运行我的第一列“SQLToRun”。。。然后我将“Select*from”替换为“Delete from”，它允许我删除对该给定ID的任何引用！将这些结果写入文件，以备不时之需。

注意***在运行delete语句之前，请确保消除所有bakup表。。。

  SELECT  'Delete from ' + t.name + ' where ' + c.name + ' = 148' AS SQLToRun
  FROM sys.columns c, c.name as ColName, t.name as TableName
  JOIN sys.tables t 
     ON c.object_id = t.object_id
  WHERE c.name LIKE '%ProjectID%'

为了改进上面的答案，我还包括了视图，并将模式和表/视图连接在一起，使结果更加明显。

DECLARE @COLUMNNAME AS VARCHAR(100);

SET @COLUMNNAME = '%Absence%';

SELECT CASE
           WHEN [T].[NAME] IS NULL
           THEN 'View'
           WHEN [T].[NAME] = ''
           THEN 'View'
           ELSE 'Table'
       END AS [TYPE], '[' + [S].[NAME] + '].' + '[' + CASE
                                                          WHEN [T].[NAME] IS NULL
                                                          THEN [V].[NAME]
                                                          WHEN [T].[NAME] = ''
                                                          THEN [V].[NAME]
                                                          ELSE [T].[NAME]
                                                      END + ']' AS [TABLE], [C].[NAME] AS [COLUMN]
FROM [SYS].[SCHEMAS] AS [S] LEFT JOIN [SYS].[TABLES] AS [T] ON [S].SCHEMA_ID = [T].SCHEMA_ID
                            LEFT JOIN [SYS].[VIEWS] AS [V] ON [S].SCHEMA_ID = [V].SCHEMA_ID
                            INNER JOIN [SYS].[COLUMNS] AS [C] ON [T].OBJECT_ID = [C].OBJECT_ID
                                                                 OR
                                                                 [V].OBJECT_ID = [C].OBJECT_ID
                            INNER JOIN [SYS].[TYPES] AS [TY] ON [C].[SYSTEM_TYPE_ID] = [TY].[SYSTEM_TYPE_ID]
WHERE [C].[NAME] LIKE @COLUMNNAME
GROUP BY '[' + [S].[NAME] + '].' + '[' + CASE
                                             WHEN [T].[NAME] IS NULL
                                             THEN [V].[NAME]
                                             WHEN [T].[NAME] = ''
                                             THEN [V].[NAME]
                                             ELSE [T].[NAME]
                                         END + ']', [T].[NAME], [C].[NAME], [S].[NAME]
ORDER BY '[' + [S].[NAME] + '].' + '[' + CASE
                                             WHEN [T].[NAME] IS NULL
                                             THEN [V].[NAME]
                                             WHEN [T].[NAME] = ''
                                             THEN [V].[NAME]
                                             ELSE [T].[NAME]
                                         END + ']', CASE
                                                        WHEN [T].[NAME] IS NULL
                                                        THEN 'View'
                                                        WHEN [T].[NAME] = ''
                                                        THEN 'View'
                                                        ELSE 'Table'
                                                    END, [T].[NAME], [C].[NAME];

我刚试过，这个效果很好

USE YourDatabseName
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 '%YourColumnName%'
ORDER BY schema_name, table_name;

只将YourDatabaseName更改为数据库，将YourcolumnName更改为您要查找的列名，其余的保持原样。

希望这有所帮助

为此，我们可以使用sp_columns。

sp_columns 'table name', null, null, '%column name%'

我出于同样的目的使用了这个，它起到了作用：

  select * from INFORMATION_SCHEMA.COLUMNS
  where TABLE_CATALOG= 'theDatabase'
  and COLUMN_NAME like 'theCol%'

我不知道为什么很多人建议使用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%'