Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

2 Answers 2

up vote 4 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, 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. :)

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
add comment
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.

share|improve this answer
add comment

Your Answer

 
discard

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.