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