How to find specific column in SQL Server database?

Question

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

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

Answer 1

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

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.

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

Answer 3

You can use the Query as below:

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