SQL Server: Look for column headers

Question

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?

Answer 1

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. :)

Answer 2

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%word%'

Join to the TABLES view if you want to exclude views (TABLE_TYPE = 'BASE TABLE').

If you query sysdatabases or sys.databases you can construct SQL to do this query in each database. Just create a rowset that you can copy and paste back into the query window.