Friday, August 29, 2014

CMS: Find, in All Servers and Databases, if given Table.Column's data is unique

Using CMS, we can determine if all the Customer.CustomerID values are unique, and therefore, for example, a candidate for being made a primary key.

-- Find, in All Servers and Databases, if given Table.Column's data is unique. 
set nocount on

if left(convert(nvarchar, serverproperty('productversion')), 1) <> '8'
begin
    declare @Schema sysname     set @Schema = 'dba'
    declare @Table  sysname     set @Table  = 'Customer'
    declare @Column sysname     set @Column = 'CustomerId'

    declare @Sql nvarchar(4000)

    set @Sql = 'use [?]

                if  exists (select *
                              from sys.tables
                             where name = ''' + @Table + '''
                               and schema_id(''' + @Schema + ''') = schema_id)
                begin
                    declare @NonUniqueCount int
                    
                    select @NonUniqueCount = count(*)
                      from ' + @Schema + '.' + @Table  + '
                  group by ' + @Column + '
                    having count(*) > 1

                    if @NonUniqueCount is not null
                    begin
                        print @@SERVERNAME +'': '' + ''?'' + '' - @NonUniqueCount: '' + cast(@NonUniqueCount as nvarchar(50))
                    end
                end'

    exec sp_msforeachdb @Sql
end

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