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

0 comments:

Post a Comment