[推荐] sp_helpindex2 for sql server 2005/2008 100perfectaction]

发布时间:2016-12-11 12:24:24 编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"[推荐] sp_helpindex2 for sql server 2005/2008 100perfectaction]",主要涉及到[推荐] sp_helpindex2 for sql server 2005/2008 100perfectaction]方面的内容,对于[推荐] sp_helpindex2 for sql server 2005/2008 100perfectaction]感兴趣的同学可以参考一下。

分享两个新的sp_helpindex: For SQL Server 2005 /*============================================================================   File:     sp_helpindex2.sql   Summary:  So, what are the included columns?! This is a MODIFIED sp_helpindex script that includes INCLUDEd columns.      Date:     August 2008   SQL Server *2005* Version: tested on 9.00.3068.00 (SP2+ GDRs) ------------------------------------------------------------------------------   Written by Kimberly L. Tripp, SYSolutions, Inc.  (with tweaks/fixes from blog readers! THANKS!!)   For more scripts and sample code, check out      http://www.SQLskills.com   This script is intended only as a supplement to demos and lectures   given by SQLskills instructors.        THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF    ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED    TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A   PARTICULAR PURPOSE. ============================================================================*/ USE master go IF OBJECTPROPERTY(object_id('sp_helpindex2'), 'IsProcedure') = 1 DROP PROCEDURE sp_helpindex2 go create procedure sp_helpindex2 @objname nvarchar(776) -- the table to check for indexes as -- April 2008: Updated to add included columns to the output.  -- August 2008: Fixed a bug (missing begin/end block) AND I found -- a few other issues that people hadn't noticed (yikes!)! -- See Kimberly's blog for updates and/or additional information -- http://www.SQLskills.com/blogs/Kimberly -- PRELIM set nocount on declare @objid int, -- the object id of the table @indid smallint, -- the index id of an index @groupid int,   -- the filegroup id of an index @indname sysname, @groupname sysname, @status int, @keys nvarchar(2126), --Length (16*max_identifierLength)+(15*2)+(16*3) @inc_columns nvarchar(max), @inc_Count smallint, @loop_inc_Count smallint, @dbname sysname, @ignore_dup_key bit, @is_unique bit, @is_hypothetical bit, @is_primary_key bit, @is_unique_key  bit, @auto_created bit, @no_recompute bit -- Check to see that the object names are local to the current database. select @dbname = parsename(@objname,3) if @dbname is null select @dbname = db_name() else if @dbname <> db_name() begin raiserror(15250,-1,-1) return (1) end -- Check to see the the table exists and initialize @objid. select @objid = object_id(@objname) if @objid is NULL begin raiserror(15009,-1,-1,@objname,@dbname) return (1) end -- OPEN CURSOR OVER INDEXES (skip stats: bug shiloh_51196) declare ms_crs_ind cursor local static for select i.index_id, i.data_space_id, i.name, i.ignore_dup_key, i.is_unique, i.is_hypothetical, i.is_primary_key, i.is_unique_constraint, s.auto_created, s.no_recompute from sys.indexes i join sys.stats s on i.object_id = s.object_id and i.index_id = s.stats_id where i.object_id = @objid open ms_crs_ind fetch ms_crs_ind into @indid, @groupid, @indname, @ignore_dup_key, @is_unique, @is_hypothetical, @is_primary_key, @is_unique_key, @auto_created, @no_recompute -- IF NO INDEX, QUIT if @@fetch_status < 0 begin deallocate ms_crs_ind raiserror(15472,-1,-1,@objname) -- Object does not have any indexes. return (0) end -- create temp tables CREATE TABLE #spindtab ( index_name sysname collate database_default NOT NULL, index_id int, ignore_dup_key bit, is_unique bit, is_hypothetical bit, is_primary_key bit, is_unique_key bit, auto_created bit, no_recompute bit, groupname sysname collate database_default NULL, index_keys nvarchar(2126) collate database_default NOT NULL, -- see @keys above for length descr inc_Count smallint, inc_columns nvarchar(max) ) CREATE TABLE #IncludedColumns ( RowNumber smallint, [Name] nvarchar(128) ) -- Now check out each index, figure out its type and keys and -- save the info in a temporary table that we'll print out at the end. while @@fetch_status >= 0 begin -- First we'll figure out what the keys are. declare @i int, @thiskey nvarchar(131) -- 128+3 select @keys = index_col(@objname, @indid, 1), @i = 2 if (indexkey_property(@objid, @indid, 1, 'isdescending') = 1) select @keys = @keys  + '(-)' select @thiskey = index_col(@objname, @indid, @i) if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1)) select @thiskey = @thiskey + '(-)' while (@thiskey is not null ) begin select @keys = @keys + ', ' + @thiskey, @i = @i + 1 select @thiskey = index_col(@objname, @indid, @i) if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1)) select @thiskey = @thiskey + '(-)' end -- Second, we'll figure out what the included columns are. SELECT @inc_Count = count(*) FROM sys.tables AS tbl INNER JOIN sys.indexes AS i  ON (i.index_id > 0  and i.is_hypothetical = 0)  AND (i.object_id=tbl.object_id) INNER JOIN sys.index_columns AS ic  ON (ic.column_id > 0  and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0))  AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id) INNER JOIN sys.columns AS clmns  ON clmns.object_id = ic.object_id  and clmns.column_id = ic.column_id WHERE ic.is_included_column = 1 and (i.index_id = @indid) and (tbl.object_id = @objid)  SET @inc_Columns = NULL IF @inc_Count > 0 BEGIN DELETE FROM #IncludedColumns INSERT #IncludedColumns SELECT ROW_NUMBER() OVER (ORDER BY clmns.column_id)  , clmns.name  FROM sys.tables AS tbl INNER JOIN sys.indexes AS si  ON (si.index_id > 0  and si.is_hypothetical = 0)  AND (si.object_id=tbl.object_id) INNER JOIN sys.index_columns AS ic  ON (ic.column_id > 0  and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0))  AND (ic.index_id=CAST(si.index_id AS int) AND ic.object_id=si.object_id) INNER JOIN sys.columns AS clmns  ON clmns.object_id = ic.object_id  and clmns.column_id = ic.column_id WHERE ic.is_included_column = 1 and (si.index_id = @indid) and  (tbl.object_id= @objid) ORDER BY 1 SELECT @inc_columns = [Name]  FROM #IncludedColumns  WHERE RowNumber = 1 SET @loop_inc_Count = 1 WHILE @loop_inc_Count < @inc_Count BEGIN SELECT @inc_columns = @inc_columns + ', ' + [Name]  FROM #IncludedColumns WHERE RowNumber = @loop_inc_Count + 1 SET @loop_inc_Count = @loop_inc_Count + 1 END END select @groupname = null select @groupname = name from sys.data_spaces where data_space_id = @groupid -- INSERT ROW FOR INDEX insert into #spindtab values (@indname, @indid, @ignore_dup_key, @is_unique, @is_hypothetical, @is_primary_key, @is_unique_key, @auto_created, @no_recompute, @groupname, @keys, @inc_Count, @inc_columns) -- Next index fetch ms_crs_ind into @indid, @groupid, @indname, @ignore_dup_key, @is_unique, @is_hypothetical, @is_primary_key, @is_unique_key, @auto_created, @no_recompute end deallocate ms_crs_ind -- DISPLAY THE RESULTS select 'index_name' = index_name, 'index_description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group case when index_id = 1 then 'clustered' else 'nonclustered' end + case when ignore_dup_key <>0 then ', ignore duplicate keys' else '' end + case when is_unique <>0 then ', unique' else '' end + case when is_hypothetical <>0 then ', hypothetical' else '' end + case when is_primary_key <>0 then ', primary key' else '' end + case when is_unique_key <>0 then ', unique key' else '' end + case when auto_created <>0 then ', auto create' else '' end + case when no_recompute <>0 then ', stats no recompute' else '' end + ' located on ' + groupname), 'index_keys' = index_keys, --'num_included_columns' = inc_Count, 'included_columns' = inc_columns from #spindtab order by index_name return (0) -- sp_helpindex2 go exec sys.sp_MS_marksystemobject 'sp_helpindex2' 示例: --DROP TABLE tbl1 --GO CREATE TABLE tbl1( c1 int, c2 int, c3 int, c4 int) GO CREATE INDEX ix_1 ON tbl1(c1) INCLUDE (c2) CREATE INDEX ix_2 ON tbl1(c1) CREATE INDEX ix_3 ON tbl1(c1) INCLUDE (c2, c3) CREATE INDEX ix_4 ON tbl1(c1, c3) INCLUDE (c2) CREATE INDEX ix_5 ON tbl1(c3) INCLUDE (c1, c2, c4) CREATE INDEX ix_6 ON tbl1(c1, c2) INCLUDE (c3, c4) go sp_helpindex2 tbl1 go /* index_name index_description index_keys included_columns ix_1 nonclustered located on PRIMARY c1 c2 ix_2 nonclustered located on PRIMARY c1 NULL ix_3 nonclustered located on PRIMARY c1 c2, c3 ix_4 nonclustered located on PRIMARY c1, c3 c2 ix_5 nonclustered located on PRIMARY c3 c1, c2, c4 ix_6 nonclustered located on PRIMARY c1, c2 c3, c4 */

学习~~

学习

For SQL Server 2008 /*============================================================================   File:     sp_helpindex2.sql   Summary:  So, what are the included columns?! This is a MODIFIED sp_helpindex script that includes INCLUDEd columns AND filtered index columns.      Date:     August 2008   SQL Server *2008* Version: 10.00.1600.22 (SQL Server 2008 RTM) ------------------------------------------------------------------------------   Written by Kimberly L. Tripp, SYSolutions, Inc.   For more scripts and sample code, check out      http://www.SQLskills.com   This script is intended only as a supplement to demos and lectures   given by SQLskills instructors.        THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF    ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED    TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A   PARTICULAR PURPOSE. ============================================================================*/ USE master go create procedure sp_helpindex2 @objname nvarchar(776) -- the table to check for indexes as -- April 2008: Updated to add included columns to the output.  -- August 2008: Fixed a bug (missing begin/end block) AND I found -- a few other issues that people hadn't noticed (yikes!)! -- See Kimberly's blog for updates and/or additional information -- http://www.SQLskills.com/blogs/Kimberly -- PRELIM set nocount on declare @objid int, -- the object id of the table @indid smallint, -- the index id of an index @groupid int,   -- the filegroup id of an index @indname sysname, @groupname sysname, @status int, @keys nvarchar(2126), --Length (16*max_identifierLength)+(15*2)+(16*3) @inc_columns nvarchar(max), @inc_Count smallint, @loop_inc_Count smallint, @dbname sysname, @ignore_dup_key bit, @is_unique bit, @is_hypothetical bit, @is_primary_key bit, @is_unique_key  bit, @auto_created bit, @no_recompute bit, @filter_definition nvarchar(max) -- Check to see that the object names are local to the current database. select @dbname = parsename(@objname,3) if @dbname is null select @dbname = db_name() else if @dbname <> db_name() begin raiserror(15250,-1,-1) return (1) end -- Check to see the the table exists and initialize @objid. select @objid = object_id(@objname) if @objid is NULL begin raiserror(15009,-1,-1,@objname,@dbname) return (1) end -- OPEN CURSOR OVER INDEXES (skip stats: bug shiloh_51196) declare ms_crs_ind cursor local static for select i.index_id, i.data_space_id, i.name, i.ignore_dup_key, i.is_unique, i.is_hypothetical, i.is_primary_key, i.is_unique_constraint, s.auto_created, s.no_recompute, i.filter_definition from sys.indexes i join sys.stats s on i.object_id = s.object_id and i.index_id = s.stats_id where i.object_id = @objid open ms_crs_ind fetch ms_crs_ind into @indid, @groupid, @indname, @ignore_dup_key, @is_unique, @is_hypothetical, @is_primary_key, @is_unique_key, @auto_created, @no_recompute, @filter_definition -- IF NO INDEX, QUIT if @@fetch_status < 0 begin deallocate ms_crs_ind raiserror(15472,-1,-1,@objname) -- Object does not have any indexes. return (0) end -- create temp tables CREATE TABLE #spindtab ( index_name sysname collate database_default NOT NULL, index_id int, ignore_dup_key bit, is_unique bit, is_hypothetical bit, is_primary_key bit, is_unique_key bit, auto_created bit, no_recompute bit, groupname sysname collate database_default NULL, index_keys nvarchar(2126) collate database_default NOT NULL, -- see @keys above for length descr filter_definition nvarchar(max), inc_Count smallint, inc_columns nvarchar(max) ) CREATE TABLE #IncludedColumns ( RowNumber smallint, [Name] nvarchar(128) ) -- Now check out each index, figure out its type and keys and -- save the info in a temporary table that we'll print out at the end. while @@fetch_status >= 0 begin -- First we'll figure out what the keys are. declare @i int, @thiskey nvarchar(131) -- 128+3 select @keys = index_col(@objname, @indid, 1), @i = 2 if (indexkey_property(@objid, @indid, 1, 'isdescending') = 1) select @keys = @keys  + '(-)' select @thiskey = index_col(@objname, @indid, @i) if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1)) select @thiskey = @thiskey + '(-)' while (@thiskey is not null ) begin select @keys = @keys + ', ' + @thiskey, @i = @i + 1 select @thiskey = index_col(@objname, @indid, @i) if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1)) select @thiskey = @thiskey + '(-)' end -- Second, we'll figure out what the included columns are. SELECT @inc_Count = count(*) FROM sys.tables AS tbl INNER JOIN sys.indexes AS si  ON (si.index_id > 0  and si.is_hypothetical = 0)  AND (si.object_id=tbl.object_id) INNER JOIN sys.index_columns AS ic  ON (ic.column_id > 0  and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0))  AND (ic.index_id=CAST(si.index_id AS int) AND ic.object_id=si.object_id) INNER JOIN sys.columns AS clmns  ON clmns.object_id = ic.object_id  and clmns.column_id = ic.column_id WHERE ic.is_included_column = 1 and (si.index_id = @indid) and  (tbl.object_id= @objid) IF @inc_Count > 0 BEGIN DELETE FROM #IncludedColumns INSERT #IncludedColumns SELECT ROW_NUMBER() OVER (ORDER BY clmns.column_id)  , clmns.name  FROM sys.tables AS tbl INNER JOIN sys.indexes AS si  ON (si.index_id > 0  and si.is_hypothetical = 0)  AND (si.object_id=tbl.object_id) INNER JOIN sys.index_columns AS ic  ON (ic.column_id > 0  and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0))  AND (ic.index_id=CAST(si.index_id AS int) AND ic.object_id=si.object_id) INNER JOIN sys.columns AS clmns  ON clmns.object_id = ic.object_id  and clmns.column_id = ic.column_id WHERE ic.is_included_column = 1 and (si.index_id = @indid) and  (tbl.object_id= @objid) SELECT @inc_columns = [Name] FROM #IncludedColumns WHERE RowNumber = 1 SET @loop_inc_Count = 1 WHILE @loop_inc_Count < @inc_Count BEGIN SELECT @inc_columns = @inc_columns + ', ' + [Name]  FROM #IncludedColumns WHERE RowNumber = @loop_inc_Count + 1 SET @loop_inc_Count = @loop_inc_Count + 1 END END select @groupname = null select @groupname = name from sys.data_spaces where data_space_id = @groupid -- INSERT ROW FOR INDEX insert into #spindtab values (@indname, @indid, @ignore_dup_key, @is_unique, @is_hypothetical, @is_primary_key, @is_unique_key, @auto_created, @no_recompute, @groupname, @keys, @filter_definition, @inc_Count, @inc_columns) -- Next index fetch ms_crs_ind into @indid, @groupid, @indname, @ignore_dup_key, @is_unique, @is_hypothetical, @is_primary_key, @is_unique_key, @auto_created, @no_recompute, @filter_definition end deallocate ms_crs_ind -- DISPLAY THE RESULTS select 'index_name' = index_name, 'index_description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group case when index_id = 1 then 'clustered' else 'nonclustered' end + case when ignore_dup_key <>0 then ', ignore duplicate keys' else '' end + case when is_unique <>0 then ', unique' else '' end + case when is_hypothetical <>0 then ', hypothetical' else '' end + case when is_primary_key <>0 then ', primary key' else '' end + case when is_unique_key <>0 then ', unique key' else '' end + case when auto_created <>0 then ', auto create' else '' end + case when no_recompute <>0 then ', stats no recompute' else '' end + ' located on ' + groupname), 'index_keys' = index_keys, --'num_included_columns' = inc_Count, 'included_columns' = inc_columns, 'filter_definition' = filter_definition from #spindtab order by index_name return (0) -- sp_helpindexwinc2 go exec sys.sp_MS_marksystemobject 'sp_helpindex2'

2008示例:     --DROP TABLE tbl1     --GO     CREATE TABLE tbl1( c1 int, c2 int, c3 int, c4 int)     CREATE INDEX ix_1 ON tbl1(c1) INCLUDE (c2)     CREATE INDEX ix_2 ON tbl1(c1)     CREATE INDEX ix_3 ON tbl1(c1) INCLUDE (c2, c3)     CREATE INDEX ix_4 ON tbl1(c1, c3) INCLUDE (c2)     CREATE INDEX ix_5 ON tbl1(c3) INCLUDE (c1, c2, c4)     CREATE INDEX ix_6 ON tbl1(c1, c2) INCLUDE (c3, c4)     CREATE INDEX ix_1f ON tbl1(c1) INCLUDE (c2)     WHERE c3 IS NOT NULL     CREATE INDEX ix_2f ON tbl1(c1)     WHERE c4 > 2     CREATE INDEX ix_3f ON tbl1(c1) INCLUDE (c2, c3)     WHERE c4 > 2 AND c1 < 50 AND c2 = 12     CREATE INDEX ix_4f ON tbl1(c1, c3) INCLUDE (c2)     WHERE c4 IS NOT NULL AND c1 = 12     CREATE INDEX ix_5f ON tbl1(c3) INCLUDE (c1, c2, c4)     WHERE c1 > 5     CREATE INDEX ix_6f ON tbl1(c1, c2) INCLUDE (c3, c4)     WHERE c4 < 20     go     sp_helpindex2 tbl1     go          /* index_name index_description index_keys included_columns filter_definition ix_1 nonclustered located on PRIMARY c1 c2 NULL ix_1f nonclustered located on PRIMARY c1 c2 ([c3] IS NOT NULL) ix_2 nonclustered located on PRIMARY c1 c2 NULL ix_2f nonclustered located on PRIMARY c1 c2 ([c4]>(2)) ix_3 nonclustered located on PRIMARY c1 c2, c3 NULL ix_3f nonclustered located on PRIMARY c1 c2, c3 ([c4]>(2) AND [c1]<(50) AND [c2]=(12)) ix_4 nonclustered located on PRIMARY c1, c3 c2 NULL ix_4f nonclustered located on PRIMARY c1, c3 c2 ([c4] IS NOT NULL AND [c1]=(12)) ix_5 nonclustered located on PRIMARY c3 c1, c2, c4 NULL ix_5f nonclustered located on PRIMARY c3 c1, c2, c4 ([c1]>(5)) ix_6 nonclustered located on PRIMARY c1, c2 c3, c4 NULL ix_6f nonclustered located on PRIMARY c1, c2 c3, c4 ([c4]<(20))            */

盖章慢慢看

学习

学习

该回复于2010-07-28 09:32:55被版主删除

你们太快了 都不让我贴完

学习

引用 9 楼 perfectaction 的回复:你们太快了 都不让我贴完 没事情 可以帮你编辑

引用 11 楼 fredrickhu 的回复:引用 9 楼 perfectaction 的回复:  你们太快了 都不让我贴完  没事情 可以帮你编辑 贴完了,睡觉去了.

学习。。

大叔好帅哦 学习

认真学习!!!!!

好啊! 何时能做到那样的水到渠成。 努力啊!

学习!!

學習~~

1 学习了

学习

感谢楼主!

学习

踩踩~

学习先

Mark

不错,收藏~~~

jf

学习

该回复于2012-08-28 09:35:18被版主删除

引用 29 楼 cciebill 的回复:学习

还没研究Filtered Index.有空看下.

还没研究Filtered Index.有空看下.

这是计算机代码吧 太专业啦

study

下载分怎么弄的啊?

可以告诉我下吗

 帮顶下!!!

学习

先学习,后看

mark

接分.

er

    CREATE INDEX ix_2f ON tbl1(c1)     WHERE c4 > 2 哇,2008可以这么写的

学习

sf

.

这么快就结了?

我还没看完呢 

。。。

果真~学些

引用 48 楼 fredrickhu 的回复:这么快就结了? 呵呵,结了赚分押宝.

该回复于2009-11-27 18:49:33被版主删除

该回复于2009-11-28 20:36:26被版主删除

使用中发现一个问题,就是included_columns列有问题: 当两个索引,其中一个有included_columns,而另一个没有时,有时会出现将上一个index的included_columns也会出现在本来没有included_columns的列中. 看了下代码,有一个地方需要修改: 找到这一行: select @groupname = null 在该行上方加一行: IF @inc_Count = 0 set @inc_columns='' 如有发现问题,请各位一同修正。

上一篇:修正sp_helpindex2 for sql 2k5/2k8一个bug. 300perfectaction]
下一篇:【求解修改标志列 计算列值】 20feixianxxx]

相关文章

相关评论