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

Rate

4 (1)

Share

Share

Rate

4 (1)