这是你问题的答案

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

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

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

我们还可以使用以下语法：-

select * from INFORMATION_SCHEMA.COLUMNS 
where COLUMN_NAME like '%clientid%' 
order by TABLE_NAME

希望这不是一个重复的答案，但我想做的是在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%'

搜索表：

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;

Create table #yourcolumndetails(
DBaseName varchar(100), 
TableSchema varchar(50), 
TableName varchar(100),
ColumnName varchar(100), 
DataType varchar(100), 
CharMaxLength varchar(100))

EXEC sp_MSForEachDB @command1='USE [?];
    INSERT INTO #yourcolumndetails SELECT
    Table_Catalog
    ,Table_Schema
    ,Table_Name
    ,Column_Name
    ,Data_Type
    ,Character_Maximum_Length
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME like ''origin'''

select * from #yourcolumndetails
Drop table #yourcolumndetails