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

您可以在SQL Server中使用以下查询。

SELECT column_name AS 'ColumnName',
       table_name  AS 'TableName'
FROM   information_schema.columns
WHERE  column_name LIKE '%columnname%'
       AND table_schema = 'schema'
ORDER  BY tablename,
          columnname;

这应该是有效的：

SELECT name 
FROM sysobjects 
WHERE id IN ( SELECT id 
              FROM syscolumns 
              WHERE name like '%column_name%' )

此外，还可以找到具有指定模式的列名。

SELECT 'DBName' AS DBName,
       column_name,
       table_name,
       table_schema
FROM   dbname.information_schema.columns
WHERE  column_name LIKE '%YourColumnName%'
       AND table_schema IN ( 'YourSchemaName' ) 

您还可以在多个数据库中找到同一列。

SELECT 'DBName1' AS DB,
       column_name,
       table_name,
       table_schema
FROM   dbname1.information_schema.columns
WHERE  column_name LIKE '%YourColumnName%'
UNION
SELECT 'DBName2' AS DB,
       column_name,
       table_name,
       table_schema
FROM   dbname2.information_schema.columns
WHERE  column_name LIKE '%YourColumnName%' 

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

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

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