生成索引信息及自动创建脚本（Sql Server 2000）

生成索引信息及自动创建脚本

 

 

create proc p_helpindex

@tbname sysname ='' ,

@CLUSTERED int = '1'

as

--生成索引信息及索引创建脚本（Sql Server 2000）

--author : happyflystone

--         Http://blog.csdn.net/happyflystone

--@tbname 表名，空返回空

--@CLUSTERED   是否显示聚集索引,1显示聚集索引,2不显示聚集索引

--调用：p_helpindex 'dbo.customers','1'

--转载请注明出处

if @tbname is null or @tbname = ''

    return -1

declare @t table(

    table_name nvarchar(100),

    schema_name nvarchar(100),

    fill_factor int,

    is_padded int,

    ix_name nvarchar(100),

    type int,

    keyno int,

    column_name nvarchar(200),

    cluster varchar(20),

    ignore_dupkey varchar(20),

    [unique] varchar(20),

    groupfile varchar(10)

)

declare

    @table_name nvarchar(100),

    @schema_name nvarchar(100),

    @fill_factor int,

    @is_padded int,

    @ix_name nvarchar(100),@ix_name_old nvarchar(100),

    @type int,

    @keyno int,

    @column_name nvarchar(100),--@column_name_temp nvarchar(500),

    @cluster varchar(20),

    @ignore_dupkey varchar(20),

    @unique varchar(20),

    @groupfile varchar(10)

declare ms_crs_ind cursor local static for

    select

        distinct table_name = a.name,

        schema_name = b.name,

        fill_factor= c.origfillfactor,

        is_padded = case when c.status = 256 then 1 else 0 end,

        ix_name = c.name,

       type =  c.indid  , d.keyno,

        column_name = e.name + case when  indexkey_property(a.id,c.indid, d.keyno, 'isdescending') =1 then ' desc ' else '' end,

       case when (c.status & 16)<>0 then 'clustered' else 'nonclustered' end,

       case when (c.status & 1) <>0 then  'IGNORE_DUP_KEY' else '' end,

       case when (c.status & 2) <>0 then  'unique' else '' end ,

        g.groupname

    from sysobjects a

    inner join sysusers b on a.uid = b.uid

    inner join sysindexes c on a.id = c.id

    inner join sysindexkeys d on a.id = d.id and c.indid = d.indid

    inner join syscolumns e on   a.id = e.id and d.colid = e.colid

    inner join sysfilegroups g on g.groupid = c.groupid

    left join master.dbo.spt_values f on f.number = c.status and f.type = 'I'

    where a.id = object_id(@tbname) and c.indid < 255

          and (c.status & 64)=0 and c.indid >= @CLUSTERED

    order by c.indid,d.keyno

 

open ms_crs_ind

fetch ms_crs_ind into @table_name ,

    @schema_name ,

    @fill_factor,

    @is_padded,

    @ix_name,

    @type ,

    @keyno,

    @column_name,

    @cluster ,

    @ignore_dupkey ,

    @unique ,

    @groupfile

 

 

if @@fetch_status < 0

begin

    deallocate ms_crs_ind

    raiserror(15472,-1,-1) --'Object does not have any indexes.'

    return -1

end

while @@fetch_status >= 0

begin

    if exists(select 1 from @t where ix_name = @ix_name)

        update @t

        set column_name = column_name+','+@column_name

        WHERE IX_NAME = @IX_NAME

    else

        insert into @t

        select @table_name ,

        @schema_name ,

        @fill_factor,

        @is_padded,

        @ix_name,

        @type ,

        @keyno,

        @column_name,

        @cluster ,

        @ignore_dupkey ,

        @unique ,

        @groupfile

    fetch ms_crs_ind into @table_name ,

        @schema_name ,

        @fill_factor,

        @is_padded,

        @ix_name,

        @type ,

        @keyno,

        @column_name,

        @cluster ,

        @ignore_dupkey ,

        @unique ,

        @groupfile

 

end

deallocate ms_crs_ind

 

select 'CREATE '+upper([unique])+

     case when [unique] = '' then '' else ' ' end+upper(cluster)+' INDEX '+ix_name+' ON '+table_name+'('+column_name+')' +

     case when fill_factor > 0 or is_padded = 1 or (upper(cluster) != 'NONCLUSTERED' and ignore_dupkey = 'IGNORE_DUP_KEY' )

     then ' WITH '

       +case when is_padded = 1 then 'PAD_INDEX,' else '' end

        +case when fill_factor > 0 then 'FILLFACTOR ='+ltrim(fill_factor) else '' end

        +case when ignore_dupkey = 'IGNORE_DUP_KEY' and upper(cluster) = 'NONCLUSTERED'

              then case when  (fill_factor > 0 or is_padded = 1)

                   then ',IGNORE_DUP_KEY' else ',IGNORE_DUP_KEY' end

              else '' end

     else '' end

+' ON ['+  groupfile+']' as col

from @t

return 0

go

 

 

以下为测试

 

 

--test

create table tb_test(id int,dept varchar(20))

go

CREATE UNIQUE CLUSTERED INDEX idx_6 ON tb_test(id) ON [PRIMARY]

CREATE NONCLUSTERED INDEX idx_1 ON tb_test(id) ON [PRIMARY]

CREATE NONCLUSTERED INDEX idx_2 ON tb_test(id) WITH PAD_INDEX,FILLFACTOR =90 ON [PRIMARY]

CREATE NONCLUSTERED INDEX idx_3 ON tb_test(id) WITH FILLFACTOR =90 ON [PRIMARY]

CREATE NONCLUSTERED INDEX idx_4 ON tb_test(id,dept) ON [PRIMARY]

CREATE NONCLUSTERED INDEX idx_5 ON tb_test(dept,id) ON [PRIMARY]

CREATE NONCLUSTERED INDEX idx_7 ON tb_test(id,dept desc ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX idx_8 ON tb_test(id desc ,dept) ON [PRIMARY]

go

 

exec p_helpindex 'tb_test'

drop proc p_helpindex

drop table tb_test

 

/*

 

col

-----------------------------------------------------

CREATE UNIQUE CLUSTERED INDEX idx_6 ON tb_test(id) ON [PRIMARY]

CREATE NONCLUSTERED INDEX idx_1 ON tb_test(id) ON [PRIMARY]

CREATE NONCLUSTERED INDEX idx_2 ON tb_test(id) WITH FILLFACTOR =90 ON [PRIMARY]

CREATE NONCLUSTERED INDEX idx_3 ON tb_test(id) WITH FILLFACTOR =90 ON [PRIMARY]

CREATE NONCLUSTERED INDEX idx_4 ON tb_test(id,dept) ON [PRIMARY]

CREATE NONCLUSTERED INDEX idx_5 ON tb_test(dept,id) ON [PRIMARY]

CREATE NONCLUSTERED INDEX idx_7 ON tb_test(id,dept desc ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX idx_8 ON tb_test(id desc ,dept) ON [PRIMARY]

 

*/