SQL Server: search a column name in all databases
Search for a column by name pattern in all databases of an instance with sp_MSforeachdb.
Search for a column name (LIKE pattern) in all the databases of an instance. The queries use sp_MSforeachdb, an undocumented procedure that runs a block in each database.
Search all databases
sp_MSforeachdb 'select
''?'' AS DatabaseName, o.name AS TableName,c.name AS ColumnName
from sys.columns c
inner join [?].sys.objects o on c.object_id=o.object_id
WHERE c.name like ''%ColumnName%''
order by o.name,c.column_id'
Search the master database only
select
'master.'+sh.name+'.'+o.name,c.name,c.column_id
from master.sys.columns c
inner join [master].sys.objects o on c.object_id=o.object_id
INNER JOIN [master].sys.schemas sh on o.schema_id=sh.schema_id
Notes
- Replace
%ColumnName%with the pattern to search for. - The second query reads only the
masterdatabase. For another database, use the first query.