Rajanand Ilangovan
Rajanand Ilangovan

Rajanand Ilangovan

How to search for a table or column in all the databases in a server

Using T-SQL in SQL Server

Rajanand Ilangovan's photo
Rajanand Ilangovan
·Nov 17, 2022·

1 min read

To search for a table across databases in a server

EXEC sp_MSforeachdb 
'SELECT "?" AS DB, * 
FROM [?].sys.tables 
WHERE name like ''%invoice%'''

Sample output: fdhATJx5hX.png

To search for a column in tables across databases in a server

EXEC sp_MSforeachdb 
'SELECT "?" AS DB, object_name(object_id,db_id("?")) as TableName, * 
FROM [?].sys.all_columns 
WHERE name like ''%invoice%'''