Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
You could also look at using the (free) Redgate SQL Search addin for this. – Martin Smith Aug 16 '12 at 10:12
up vote 5 down vote accepted

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

select 'union all select ''' + name + ''' as dbname, + ''.'' + + ''.'' + 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 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. :)

share|improve this answer
You could also build a @sql variable, use STUFF to strip the leading UNION ALL, then execute it all in one shot, to avoid the man-handling of the output. :-) – Aaron Bertrand Aug 16 '12 at 12:50
Yes - but for a once-off, this is an easy option. – Rob Farley Aug 17 '12 at 1:45

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.