Locate missing columns across entire server

,

This script will help you locate columns that you provide in a comma delimited format.  You pass it as few or as many columns as you want in the following format: ',,....' and this will generate you a report that states which database, and table each column you passed is in. 

use master

declare @DB_NAME varchar(128)

declare @ColNamesSearchingFor varchar(128)
declare @TabName varchar(128)
declare @ErrorText varchar(1000)

set @ColNamesSearchingFor = '' -- ie 'COL1,COL2,COL3,COL4'

set @ColNamesSearchingFor = REPLACE( @ColNamesSearchingFor, '  ', ' ' )
set @ColNamesSearchingFor = REPLACE( @ColNamesSearchingFor, ', ', ',' )
set @ColNamesSearchingFor = REPLACE( @ColNamesSearchingFor, ',', ''',''' )

declare DBCur cursor 
for
	select name 
	from sysdatabases
	where name not in ('Northwind', 
					   'pubs', 
					   'msdb', 
					   'tempdb', 
					   'model', 
					   'master')

open DBCur

fetch next from DBCur into @DB_NAME

while @@FETCH_STATUS = 0 
	begin
		if @ColNamesSearchingFor = ''
			begin
			  set @ErrorText = 'invalid column name'
		 	  GOTO ERROR
			end

		PRINT '-- SEARCHING DATABASE: ' + @DB_NAME + ' --'
		exec('use ' + @DB_NAME + '
			 declare @TabName varchar(128)
			 declare @ColName varchar(128)

			 if exists (select so.name
				   from sysobjects so 
				   inner join syscolumns sc
						on so.id = sc.id
				   where sc.name in ( ''' + @ColNamesSearchingFor + ''' ) )
			begin
				declare TabCur cursor for
				select so.name, sc.name
				from sysobjects so inner join
				syscolumns sc on sc.id = so.id
				where sc.name in ( ''' + @ColNamesSearchingFor + ''' )
				order by so.name, sc.name
				
				open TabCur
				fetch next from TabCur into @TabName, @ColName

				while @@FETCH_STATUS = 0
					begin
						PRINT REPLICATE(CHAR(9), 2) + ''COLUMN '' + @ColName + '''' + 
						  '' FOUND IN TABLE: '' + @TabName
						fetch next from TabCur into @TabName, @ColName
					end
				close TabCur
				deallocate TabCur
				PRINT REPLICATE(CHAR(13), 2)
			end
				else
					PRINT REPLICATE(CHAR(9), 2) + ''NO TABLES FOUND WITH THOSE COLUMNS! '' + REPLICATE(CHAR(13), 2)
					  
			')
		fetch next from DBCur into @DB_NAME
	end

close DBCur
deallocate DBCur

error:
	PRINT @ErrorText

Rate

Share

Share

Rate