SQL SERVER中找出拙劣的約束，索引，外來鍵

iSQlServer發表於2009-12-29
SQLServer索引

　　ShowColumnUsage是一個能呈現table中constraints, indexes, foreign keys所有影響的列儲存過程。

例如我們執行：

exec ShowColumnUsage 'BillOfMaterials'
.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }

那麼將返回這些資訊：

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 '%'
.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }  

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
 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-623877/，如需轉載，請註明出處，否則將追究法律責任。

相關文章