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

[-狙击手-]

create proc p_helpindex
@tbname sysname ='' ,
@CLUSTERED int = '1'
as
--生成索引信息及索引创建脚本
--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]

*/

[lg3605119]

up

[水族杰纶]

頂石頭~~

[lg3605119]

再顶石头

[-狙击手-]

更多内容请看blog :

http://blog.csdn.net/happyflystone/archive/2009/04/28/4131689.aspx