如何在SQL Server数据库中查找特定列？

Is there any way or tool in SQL Server 2016 to find a column in the entire database based on the name?

SQL Server 2016中是否有任何方法或工具可以根据名称在整个数据库中查找列？

Example: find ProductNumber and it shows you all the table(s) that have it

示例：查找ProductNumber，它会显示拥有它的所有表

5 个解决方案

#1

You can query the database's information_schema.columns table which holds the schema structure of all columns defined in your database.

您可以查询数据库的information_schema.columns表，该表包含数据库中定义的所有列的模式结构。

Using this query:

使用此查询：

select * from information_schema.columns where column_name = 'ProductNumber'

The result would give you the columns:

结果会给你列：

TABLE_NAME, TABLE_CATALOG, DATA_TYPE and more properties for this database column.

TABLE_NAME，TABLE_CATALOG，DATA_TYPE以及此数据库列的更多属性。

#2

Several options are available to you:

您可以使用以下几种方法：

SELECT * 
FROM information_schema.columns 
WHERE column_name = 'YourColumnName';

-- OR

SELECT col.name, tab.name
FROM sys.columns col
INNER JOIN sys.tables tab
  ON tab.object_id = col.object_id
WHERE col.name = 'YourColumnName';

#3

You can use the Query as below:

您可以使用以下查询：

select * 
from information_schema.columns 
where column_name = 'ProductNumber'

#4

If you need to find database objects (e.g. tables, columns, triggers) by name - have a look at the FREE Red-Gate tool called SQL Search which does this - it searches your entire database for any kind of string(s).

如果您需要按名称查找数据库对象（例如表，列，触发器） - 请查看名为SQL Search的免费Red-Gate工具，它会搜索整个数据库中的任何类型的字符串。

It's a great must-have tool for any DBA or database developer - did I already mention it's absolutely FREE to use for any kind of use??

对于任何DBA或数据库开发人员来说，这是一个非常必备的工具 - 我是否已经提到它可以完全免费用于任何类型的使用？

#5

SELECT
so.name "table name", 
sc.name "column name"
FROM sysobjects so
INNER JOIN syscolumns sc
ON so.id = sc.id
WHERE --so.type='U'

--and

- 和

sc.name like '%ProductN%'
order by so.name asc