0

How can I search for a given column name (e.g. like '%tag%') in all databases of a server?

Looking for some result like:

Database         TableName         ColumnName
-----------------------------------------------

I Needed from all databases in one shot, not just a single database.

Unheilig
  • 16,196
  • 193
  • 68
  • 98
Bill
  • 751
  • 2
  • 8
  • 18
  • 1
    Possible duplicate of [Find all tables containing column with specified name](http://stackoverflow.com/questions/4849652/find-all-tables-containing-column-with-specified-name) – Adam Martin Oct 06 '15 at 19:11

1 Answers1

0
    declare @query nvarchar(max), @crlf nvarchar(2), @columnName nvarchar(50)
    set @query=''
    set @crlf=char(13)

    /*Set the name you are searchng*/
    set @columnName='%tag%'


    SELECT @query=@query + 'select '''+name+''' as ''Datadase'', t.name as ''Table'', c.name as ''Column'' 
from ['+name+'].sys.tables t 
inner join ['++name+'].sys.columns c on t.object_id=c.object_id     
where c.name like '''+ @columnName +''' union all'+ @crlf +char(13) 
FROM master.dbo.sysdatabases

    set @query=left(@query,len(@query)-len(' union all'+ @crlf))
    exec (@query)
Horaciux
  • 6,322
  • 2
  • 22
  • 41
  • tried SELECT t.name FROM sys.tables t INNER JOIN sys.columns c on t.object_id=c.object_id WHERE c.name like'%tag%' but returned nothing. Do I select master? I guess I do need it dynamic. – Bill Oct 06 '15 at 19:21
  • Here you go, a full answer. – Horaciux Oct 08 '15 at 11:56
  • thanks. I get the following error:Msg 451, Level 16, State 1, Line 1 Cannot resolve collation conflict between "Latin1_General_CI_AS_KS_WS" and "SQL_Latin1_General_CP1_CI_AS" in UNION ALL operator occurring in SELECT statement column 2. Msg 451, Level 16, State 1, Line 1 Cannot resolve collation conflict between "Latin1_General_CI_AS_KS_WS" and "SQL_Latin1_General_CP1_CI_AS" in UNION ALL operator occurring in SELECT statement column 3. – Bill Oct 08 '15 at 16:38