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).
  1. create table #dbDetasils  
  2. (dbName varchar(100),  
  3. )  
  4. EXECUTE master.sys.sp_MSforeachdb 'USE [?];  
  5. IF (EXISTS (SELECT *  
  6. FROM INFORMATION_SCHEMA.COLUMNS  
  7. WHERE COLUMN_NAME like ''%card%''))  
  8. BEGIN  
  9. insert into #dbDetasils (dbName)  
  10. select ''?''  
  11. --print ''?''  
  12. end  
  13. '  
  14. declare @tbl table  
  15. (dbName varchar(1000),  
  16. tableName varchar(100),columnName varchar(100))  
  17. declare @query table  
  18. (  
  19. queryname nvarchar(max)  
  20. )  
  21. --select * from @tbl  
  22. insert into @query  
  23. exec ('select ''select table_catalog as dbname,table_name,column_name from ''+ dbname +'' .information_schema.columns where column_name like ''''%card%'''''' from #dbDetasils'  
  24. )  
  25. declare @queryname nvarchar(max)  
  26. declare cur cursor for  
  27. select queryname from @query  
  28. open cur  
  29. fetch next from cur into @queryname  
  30. while @@FETCH_STATUS=0  
  31. begin  
  32. insert into @tbl (dbname,tableName,columnName)  
  33. exec (@queryname)  
  34. fetch next from cur into @queryname  
  35. end  
  36. close cur  
  37. deallocate cur  
  38. select * from @tbl where dbname not in ('master','tempdb','model','msdb','ReportServer','ReportServerTempDB')  
  39. drop table #dbDetasils  
Ebook Download
View all
Learn
View all