SQL Server: Look for column headers

I'm reviewing a database server, the problem is that there are about 40 databases with each hundreds of tables. I'm looking for tables with a column name containing a specific word.

Is there a way I can do a search to identify these tables?

You want to be able to query sys.columns in each database. To do this, try:

select 'union all select ''' + name + ''' as dbname, s.name + ''.'' + o.name + ''.'' + c.name collate database_default as colname
        from ' + quotename(name) + '.sys.columns as c
        join ' + quotename(name) + '.sys.objects as o
        on o.object_id = c.object_id 
        join ' + quotename(name) + '.sys.schemas as s
        on s.schema_id = o.schema_id 
        where c.name like ''%name%''  collate database_default '
from sys.databases;

Then copy the column you get back into a new window, strip the leading "union all", and run it. :)

StackExchange Database Administrators Q#22586, answer score: 5