Get Specific Column Name In All Databases Except System Database

Sometimes, we face some requirement /scenario to find a particular column in multiple databases. Let's say, if you have multiple databases (approx. 10 + databases) and you have to find a column with name payementID, it's a time intensive work to find that column in all the databases by fetching records from information_schema.columns or from syscolumns joining with systables.

 

So, below is the query to find specific column names in all databases except System Databases (as per your need, you can add system databases too).

create table #dbDetasils
(dbName varchar(100),
)
EXECUTE master.sys.sp_MSforeachdb 'USE [?];
IF (EXISTS (SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME like ''%card%''))
BEGIN
insert into #dbDetasils (dbName)
select ''?''
--print ''?''
end
'
declare @tbl table
(dbName varchar(1000),
tableName varchar(100),columnName varchar(100))
declare @query table
(
queryname nvarchar(max)
)
--select * from @tbl
insert into @query
exec ('select ''select table_catalog as dbname,table_name,column_name from ''+ dbname +'' .information_schema.columns where column_name like ''''%card%'''''' from #dbDetasils'
)
declare @queryname nvarchar(max)
declare cur cursor for
select queryname from @query
open cur
fetch next from cur into @queryname
while @@FETCH_STATUS=0
begin
insert into @tbl (dbname,tableName,columnName)
exec (@queryname)
fetch next from cur into @queryname
end
close cur
deallocate cur
select * from @tbl where dbname not in ('master','tempdb','model','msdb','ReportServer','ReportServerTempDB')
drop table #dbDetasils