TSQL Find Column

It was irritating searching for a column on multiple databases. So I needed a solution to check each database quickly.

if object_id('tempdb..#eaa') is not null drop table #eaa;

CREATE TABLE #eaa(
    db [VARCHAR](100) NULL,
    table_name [VARCHAR](200) NULL,
    column_name [VARCHAR](200) NULL
)

GO

declare @cmd varchar(500)
set @cmd='

BEGIN

use [?];
insert into #eaa
SELECT
    DB_NAME() AS db,
    object_schema_name(t.object_id) + ''.'' + t.name as table_name,
    c.name as column_name
from sys.tables t
inner join sys.columns c
on t.object_id = c.object_id
where
    db_name() not in (''master'',''tempdb'',''msdb'') and
    c.name like ''%fail%''
end
'
exec sp_MSforeachdb @cmd

SELECT * FROM #eaa;