This CMS (Central Management Server) code will tell you, for all databases in all servers, which tables of a given name don't have a column of a given name.
-- For all databases in all servers that have a "Customer" table, which ones don't have a "ZipCode" column?
set nocount on
if left(convert(nvarchar, serverproperty('productversion')), 1) <> '8'
begin
declare @Schema sysname set @Schema = 'dbo'
declare @Table sysname set @Table = 'Customer'
declare @Column sysname set @Column = 'ZipCode'
declare @Sql nvarchar(4000)
set @Sql = 'use [?]
if exists (select *
from sys.tables
where name = ''' + @Table + '''
and schema_id(''' + @Schema + ''') = schema_id)
begin
if not exists (select *
from sys.columns
where name = ''' + @Column + '''
and object_name(object_id) = ''' + @Table + ''')
begin
print @@SERVERNAME + '': '' + ''?'' + '' - ' + @Table + ' table has no ' + @Column + ' column''
end
end'
exec sp_msforeachdb @Sql
end
0 comments:
Post a Comment