[推荐] [分享]查看索引信息/生成创建索引脚本 100perfectaction]

发布时间:2016-12-11 12:24:42 编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"[推荐] [分享]查看索引信息/生成创建索引脚本 100perfectaction]",主要涉及到[推荐] [分享]查看索引信息/生成创建索引脚本 100perfectaction]方面的内容,对于[推荐] [分享]查看索引信息/生成创建索引脚本 100perfectaction]感兴趣的同学可以参考一下。

写这个proc有两个原因: 1.是系统的sp_helpindex不能显示include列 2.在做发布数据库时,可以只发布数据,然后脚建索引的脚本生成T-sql,到订阅端去执行.   create proc p_helpindex  @tbname sysname ='' ,@type char(1) = '1' as --生成索引信息及索引创建脚本  --author : perfectaction [email protected] 表名,空返回所有表索引 [email protected]   是否显示聚集索引,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_locks from sys.tables as a inner join sys.schemas as b on a.schema_id=b.schema_id and a.is_ms_shipped=0 inner join sys.indexes as c on a.object_id=c.object_id inner join sys.index_columns as d on d.object_id=c.object_id and d.index_id=c.index_id inner join sys.columns as e on e.object_id=d.object_id and e.column_id=d.column_id inner 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>[email protected] ) 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 sqlscript from t as a outer 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 column_name="', ','), '"/>', ''), 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 index_column_id for xml auto               ), '<e column_name="', ','), '"/>', ''), 1, 1, '')   )m   order by a.schema_name,a.table_name,a.ix_name create database db_test go use db_test go create table tb(id int primary key,col_1 varchar(20),col_2 varchar(30),col_3 varchar(30)) go insert into tb select 1,'a','b','c' go create 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) */ 如有问题,请指正。

学习 放进偶的网摘.

学习 谢完美兄~~

美美牛人。顶个

UP

学习,lz好人.

学习

学习

学习

楼主牛人 佩服

楼主,好人!

帮顶     致敬

ding

本来想把索引数据页和碎片也显示出来,但 sys.dm_db_index_physical_stats 动态管理函数不接受来自 CROSS APPLY 和 OUTER APPLY 的相关参数,所以暂时没加入。

何时能达到楼主这种境界啊,

sf

顶,支持美美

mark,新人来学习,顺便接分。

hehe 

支持,分享快乐~

学习了,收藏了~~ 

learn

up

学习了

力顶你!好啊

学习中……

你太帅了啊!!!

你太厉害的啦、、

支持……

.

该回复于2009-03-11 16:02:21被版主删除

果然很强悍!学习....

谢谢分享!

好东西 哈哈 

学习,学习

强大 老牛了

厲害!

没分了,顶一下

该回复于2011-01-06 16:08:43被版主删除

学习学习

上一篇:SQL存储过程显示树形菜单 60zhangzhen_927116]
下一篇:高手进来看看 关于触发器触发删除下级时出现的问题 40wangm_521]

相关文章

相关评论