SQLSERVER中找出拙劣的约束，索引，外键

　　ShowColumnUsage是一个能呈现table中constraints, indexes, foreign keys所有影响的列存储过程。

例如我们执行：

exec ShowColumnUsage 'BillOfMaterials'

那么将返回这些信息：

Heading	ColumnName	Constraints	Indexes	ForeignKeys
BillOfMaterials
	BillOfMaterialsID	pk2
	ProductAssemblyID		udx1.1	fk1
	ComponentID		udx1.2	fk2
	StartDate		udx1.3
	EndDate
	UnitMeasureCode		idx3	fk3
	BOMLevel
	PerAssemblyQty
	ModifiedDate

 

你还可以使用　查询所有table:

exec ShowColumnUsage '%'

 

ShowColumnUsage　的　T-sql:

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

好了现在　在你数据库中查找你没有见过的错误方便多了。希望这篇POST对你有帮助。

Author:Petter Liu     http://wintersun.cnblogs.com