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