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

create proc p_helpindex @tbname sysname = ,@type char(1) = 1as–生成索引信息及索引创建脚本–@tbname 表名，空返回所有表索引–@type 是否显示聚集索引,1显示聚集索引,2不显示聚集索引–调用：p_helpindex dbo.customers,1with t as (select rank() ove

create proc p_helpindex @tbname sysname ='' ,@type char(1) = '1'as--生成索引信息及索引创建脚本--@tbname 表名，空返回所有表索引--@type   是否显示聚集索引,1显示聚集索引,2不显示聚集索引--调用：p_helpindex 'dbo.customers','1'with t as (select rank() over (order by b.name,a.name,c.name) as id,c.index_id, b.name as schema_name,a.name as table_name,c.fill_factor,c.is_padded,c.name as ix_name,c.type,e.name as column_name,d.index_column_id,c.is_primary_key,d.is_included_column,f.name as filegroup_name,c.is_unique,c.ignore_dup_key,d.is_descending_key as is_descending_key,c.allow_row_locks,c.allow_page_locksfrom sys.tables as ainner join sys.schemas as b on a.schema_id=b.schema_id and a.is_ms_shipped=0inner join sys.indexes as c on a.object_id=c.object_idinner join sys.index_columns as d on d.object_id=c.object_id and d.index_id=c.index_idinner join sys.columns as e on e.object_id=d.object_id and e.column_id=d.column_idinner join sys.data_spaces as f on f.data_space_id=c.data_space_id where a.object_id like '%'+isnull(ltrim(object_id(@tbname)),'')+'%'and c.is_hypothetical=0 and is_disabled=0 and c.type>=@type)select distinct a.schema_name,a.table_name,a.ix_name,case a.type when 1 then 'clustered' when 2 then 'nonclustered' else '' end as index_type,case a.is_primary_key when 0 then 'no' else 'yes' end as is_primary_key,m.ix_index_column_name,isnull(m.ix_index_include_column_name,'') as ix_index_include_column_name,a.filegroup_name,replace('create '+ case when is_unique=1 then 'unique ' else '' end + case when a.type=1 then 'clustered' else 'nonclustered' end  +' index '+ a.ix_name+' on '+a.schema_name+'.'+a.table_name+'('+m.ix_index_column_name+')'+ case when m.ix_index_include_column_name is null then '' else 'include('+m.ix_index_include_column_name+')'end + case when fill_factor>0 or ignore_dup_key=1 or is_padded=1 or allow_row_locks=0 or allow_page_locks=0 then 'with(' else '' end+ case when fill_factor>0 then ',fillfactor='+rtrim(fill_factor) else '' end + case when is_padded=1 then ',pad_index=on' else '' end + case when ignore_dup_key=1 then ',ignore_dup_key=on' else '' end + case when allow_row_locks=0 then ',allow_row_locks=off' else '' end+ case when allow_page_locks=0 then ',allow_page_locks=off' else '' end+ case when fill_factor>0 or ignore_dup_key=1 or is_padded=1 or allow_row_locks=0 or allow_page_locks=0 then ')' else '' end,'with(,','with(')as sqlscriptfrom t as aouter apply  (      select ix_index_column_name= stuff(replace(replace(              (                  select case when b.is_descending_key =1 then column_name + ' desc' else column_name end as column_name                  from t as b where a.id=b.id and is_included_column=0 order by index_column_id for xml auto              ), '<b />', ''), 1, 1, '')              ,ix_index_include_column_name= stuff(replace(replace(              (                  select column_name from t as b where a.id=b.id and is_included_column=1                  order by <i>本文来源gaodai$ma#com搞$$代**码网</i>index_column_id for xml auto              ), '', ''), 1, 1, '')  )m  order by a.schema_name,a.table_name,a.ix_name--测试：create database db_testgouse db_testgocreate table tb(id int primary key,col_1 varchar(20),col_2 varchar(30),col_3 varchar(30))goinsert into tb select 1,'a','b','c'gocreate index ix_01 on tb(col_1)create index ix_03 on tb(col_1,col_2 desc)create index ix_02 on tb(col_1)include(col_2)with(fillfactor=80,pad_index=on)create unique index ix_04 on tb(col_1,col_3)include(col_2)with(ignore_dup_key=on)go--执行这个脚本的结果p_helpindex tb/*--生成的创建脚本create nonclustered index ix_01 on dbo.tb(col_1)create nonclustered index ix_02 on dbo.tb(col_1)include(col_2)with(fillfactor=80,pad_index=on)create nonclustered index ix_03 on dbo.tb(col_1,col_2 desc)create unique nonclustered index ix_04 on dbo.tb(col_1,col_3)include(col_2)with(ignore_dup_key=on)create unique clustered index PK__tb__7C8480AE on dbo.tb(id)*/

喜欢 (0)赏

[搞代码]

分享 (0)