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?
asked Aug 16, 2012 at 7:46
-
You could also look at using the (free) Redgate SQL Search addin for this.– Martin SmithAug 16, 2012 at 10:12
Add a comment
|
2 Answers
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. :)
-
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
-
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.
