Find botched constraints, idxs, fks
ShowColumnUsage presents table columns in a format for you to see all constraints, indexes, and foreign keys affecting each column. You'll be shocked at how many errors you'll find in your constraints and indexes within seconds of running it. I never deploy to customers without running this first.
I'll walk you through it quickly. Create the proc in msdb and do the following to see the results for all tables:
exec ShowColumnUsage '%'
Notice that all constraints and indexes numbered 1 are clustered.
Now let's see all the botched indexes in msdb...
-- non-unique clustered index is in reverse order from the foreign key definition
-- seems illogical but maybe they have their reasons
exec ShowColumnUsage 'DTA_reports_querytable'
-- idx2 is a complete waste since pk1.1 has it covered
-- only makes sense in a few critical situations
exec ShowColumnUsage 'log_shipping_primary_secondaries'
-- ak2 and udx3 are equal so one is redundant
exec ShowColumnUsage 'log_shipping_primary_databases'
-- this one is just plain weird having an alternate key that is part of a primary key???
exec ShowColumnUsage 'sysdtspackages'
Now run it in your databases and fix errors you could never see before.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ShowColumnUsage]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ShowColumnUsage]
go
create procedure [dbo].[ShowColumnUsage]
@TableNameLike varchar(128) = null
as
-- written by William Talada
if @TableNameLike is null
begin
print 'This stored procedure shows which columns of a table participate'
print 'in primary key constraints, unique constraints (alternate keys),'
print 'unique indexes, regular indexes, and foreign keys.'
print 'Any constraint or index numbered 1 is clustered.'
print ' exec ShowColumnUsage ''Ac%'''
return 0
end
set nocount on
-- List all tables and columns with their constraint columns pk, aks, fks, idxs
declare @loop int,
@loopmax int
-- get list of tables
declare
@tables table (TableName varchar(100), TableId int)
insert into
@tables
select
t.name,
t.object_id
from
sys.tables t
where
t.name like @TableNameLike
-- get list of cols
declare
@Cols table (TableId int, ColumnId int, ColumnName varchar(100), Constraints varchar(100), Indexes varchar(100), ForeignKeys varchar(100))
insert into
@Cols
select
t.TableId,
c.column_id,
c.name,
'',
'',
''
from
@tables t
join
sys.columns c on t.Tableid=c.object_id
-- get list of fk tables
declare @fks table (TableId int, FkId int, FkNbr int, FkColCnt int)
insert into
@fks
select
parent_object_id,
object_id,
0,
(select max(constraint_column_id) from sys.foreign_key_columns fkc where fk.object_id=fkc.constraint_object_id)
from
sys.foreign_keys fk
join
@tables c on fk.parent_object_id = c.TableId
-- number the fks
set @loop = 0
while @@rowcount > 0
begin
set @loop = @loop + 1
update
fks
set
FkNbr=@loop
from
@fks fks
where
fks.FkNbr=0
and
fks.FkId in
(
select
min(FkId)
from
@fks
where
FkNbr=0
group by
TableId
)
end
--select * from @fks
-- get pks
declare @pks table (TableId int, PkId int, PkNbr int, PkColCnt int)
insert into
@pks
select
i.object_id,
i.index_id,
i.index_id,
(select max(key_ordinal) from sys.index_columns ic
where i.object_id=ic.object_id and i.index_id=ic.index_id)
from
sys.indexes i
join
@tables c on i.object_id=c.TableId
where
i.is_primary_key=1
--select * from @pks
-- get aks
declare @aks table (TableId int, AkId int, AkNbr int, AkColCnt int)
insert into
@aks
select
i.object_id,
i.index_id,
i.index_id,
(select max(key_ordinal) from sys.index_columns ic
where i.object_id=ic.object_id and i.index_id=ic.index_id)
from
sys.indexes i
join
@tables c on i.object_id=c.TableId
where
i.is_unique_constraint=1
--select * from @aks
-- get udxs
declare @udxs table (TableId int, UdxId int, UdxNbr int, UdxColCnt int)
insert into
@udxs
select
i.object_id,
i.index_id,
i.index_id,
(select max(key_ordinal) from sys.index_columns ic
where i.object_id=ic.object_id and i.index_id=ic.index_id)
from
sys.indexes i
join
@tables c on i.object_id=c.TableId
where
i.is_unique_constraint=0
and
i.is_primary_key=0
and
i.is_unique=1
--select * from @udxs
-- get idxs
declare @idxs table (TableId int, IdxId int, IdxNbr int, IdxColCnt int)
insert into
@idxs
select
i.object_id,
i.index_id,
i.index_id,
(select max(index_column_id) from sys.index_columns ic
where i.object_id=ic.object_id and i.index_id=ic.index_id)
from
sys.indexes i
join
@tables c on i.object_id=c.TableId
where
i.is_unique_constraint=0
and
i.is_primary_key=0
and
i.is_unique=0
--select * from @idxs
----------------------------------------------------------------------------------
-- pk
--declare @Cols table (TableId int, ColumnId int, ColumnName varchar(100), Constraints varchar(100), Indexes varchar(100), ForeignKeys varchar(100))
--declare @pks table (TableId int, PkId int, PkNbr int, PkColCnt int)
select @loopmax = max(PkNbr) from @pks
set @loop=0
while @loop <= @loopmax
begin
update
c
set
Constraints = Constraints
+ ' pk'+case p.PkColCnt
when 1 then cast(p.PkNbr as varchar(10))
else cast(p.PkNbr as varchar(10))+'.'+cast(ic.index_column_id as varchar(10))
end
from
@cols c
join
@pks p on c.TableId=p.TableId
join
sys.index_columns ic on p.TableId = ic.object_id and p.PkId = ic.index_id and c.ColumnId = ic.column_id
where
p.PkNbr = @loop
set @loop = @loop + 1
end
-----------------
-- ak
--declare @Cols table (TableId int, ColumnId int, ColumnName varchar(100), Constraints varchar(100), Indexes varchar(100), ForeignKeys varchar(100))
--declare @aks table (TableId int, AkId int, AkNbr int, AkColCnt int)
select @loopmax = max(AkNbr) from @aks
set @loop=0
while @loop <= @loopmax
begin
update
c
set
Constraints = Constraints
+ ' ak'+case p.AkColCnt
when 1 then cast(p.AkNbr as varchar(10))
else cast(p.AkNbr as varchar(10))+'.'+cast(ic.index_column_id as varchar(10))
end
from
@cols c
join
@aks p on c.TableId=p.TableId
join
sys.index_columns ic on p.TableId = ic.object_id and p.AkId = ic.index_id and c.ColumnId = ic.column_id
where
p.AkNbr = @loop
set @loop = @loop + 1
end
-----------------
-- get udxs
--declare @Cols table (TableId int, ColumnId int, ColumnName varchar(100), Constraints varchar(100), Indexes varchar(100), ForeignKeys varchar(100))
--declare @udxs table (TableId int, UdxId int, UdxNbr int, UdxColCnt int)
select @loopmax = max(UdxNbr) from @udxs
set @loop=0
while @loop <= @loopmax
begin
update
c
set
Indexes = Indexes
+ ' udx'+case p.UdxColCnt
when 1 then cast(p.UdxNbr as varchar(10))
else cast(p.UdxNbr as varchar(10))+'.'+cast(ic.index_column_id as varchar(10))
end
from
@cols c
join
@udxs p on c.TableId=p.TableId
join
sys.index_columns ic on p.TableId = ic.object_id and p.UdxId = ic.index_id and c.ColumnId = ic.column_id
where
p.UdxNbr = @loop
set @loop = @loop + 1
end
-----------------
-- get idxs
--declare @Cols table (TableId int, ColumnId int, ColumnName varchar(100), Constraints varchar(100), Indexes varchar(100), ForeignKeys varchar(100))
--declare @idxs table (TableId int, IdxId int, IdxNbr int, IdxColCnt int)
select @loopmax = max(IdxNbr) from @idxs
set @loop=0
while @loop <= @loopmax
begin
update
c
set
Indexes = Indexes
+ ' idx'+case p.IdxColCnt
when 1 then cast(p.IdxNbr as varchar(10))
else cast(p.IdxNbr as varchar(10))+'.'+cast(ic.index_column_id as varchar(10))
end
+ case ic.is_included_column
when 1 then '+'
else ''
end
from
@cols c
join
@idxs p on c.TableId=p.TableId
join
sys.index_columns ic on p.TableId = ic.object_id and p.IdxId = ic.index_id and c.ColumnId = ic.column_id
where
p.IdxNbr = @loop
set @loop = @loop + 1
end
-----------------
-- get fks
--declare @Cols table (TableId int, ColumnId int, ColumnName varchar(100), Constraints varchar(100), Indexes varchar(100), ForeignKeys varchar(100))
--declare @fks table (TableId int, FkId int, FkNbr int, FkColCnt int)
select @loopmax = max(FkNbr) from @fks
set @loop=0
while @loop <= @loopmax
begin
update
c
set
ForeignKeys = ForeignKeys
+ ' fk'+case p.FkColCnt
when 1 then cast(p.FkNbr as varchar(10))
else cast(p.FkNbr as varchar(10))+'.'+cast(ic.constraint_column_id as varchar(10))
end
from
@cols c
join
@fks p on c.TableId=p.TableId
join
sys.foreign_key_columns ic on p.FkId = ic.constraint_object_id
and p.TableId = c.TableId and c.ColumnId = ic.parent_column_id
where
p.FkNbr = @loop
set @loop = @loop + 1
end
--select * from sys.foreign_key_columns
--
select
x.Heading,
x.ColumnName,
x.Constraints,
x.Indexes,
x.ForeignKeys
from
(
select
'' as Heading,
t.TableName,
c.ColumnId,
c.ColumnName,
c.Constraints,
c.Indexes,
c.ForeignKeys
from
@Tables t
join
@Cols c on t.TableId=c.TableId
union
select
t.tableName,
t.tableName,
0,
'',
'',
'',
''
from
@Tables t
join
@Cols c on t.TableId=c.TableId
) as x
order by
x.TableName,
x.ColumnId
return 0
go
