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 
