Friday, August 29, 2014

CMS: Find all Databases on all Servers with Table "A" without Column "B"

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