5

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?

1
  • You could also look at using the (free) Redgate SQL Search addin for this. Aug 16, 2012 at 10:12

2 Answers 2

5

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

2
  • 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. :-) Aug 16, 2012 at 12:50
  • Yes - but for a once-off, this is an easy option.
    – Rob Farley
    Aug 17, 2012 at 1:45
0
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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