LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon