6,129
社区成员
发帖
与我相关
我的任务
分享sp_helpindex2 for sql server 2005/2008
分享两个新的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
*/
...全文
请发表友善的回复…
发表回复
nzperfect 2009-12-04
- 打赏
- 举报
使用中发现一个问题，就是included_columns列有问题：
当两个索引，其中一个有included_columns，而另一个没有时，有时会出现将上一个index的included_columns也会出现在本来没有included_columns的列中.
看了下代码，有一个地方需要修改：
找到这一行：
在该行上方加一行：
如有发现问题，请各位一同修正。
当两个索引，其中一个有included_columns，而另一个没有时，有时会出现将上一个index的included_columns也会出现在本来没有included_columns的列中.
看了下代码，有一个地方需要修改：
找到这一行：
select @groupname = null在该行上方加一行：
IF @inc_Count = 0 set @inc_columns=''如有发现问题，请各位一同修正。
hery2002 2009-11-27
- 打赏
- 举报
.
nzperfect 2009-11-27
- 打赏
- 举报
[Quote=引用 48 楼 fredrickhu 的回复:]
这么快就结了？
[/Quote]
呵呵，结了赚分押宝.
这么快就结了？
[/Quote]
呵呵，结了赚分押宝.
一洽客服系统 2009-11-27
- 打赏
- 举报
果真~学些
--小F-- 2009-11-27
- 打赏
- 举报
。。。
--小F-- 2009-11-27
- 打赏
- 举报
我还没看完呢
--小F-- 2009-11-27
- 打赏
- 举报
这么快就结了？
fcuandy 2009-11-26
- 打赏
- 举报
sf
Mirana_NightShade 2009-11-26
- 打赏
- 举报
学习
dahongxing 2009-11-26
- 打赏
- 举报
这是计算机代码吧
太专业啦
太专业啦
Jones--zhao 2009-11-26
- 打赏
- 举报
还没研究Filtered Index.有空看下.
Garnett_KG 2009-11-26
- 打赏
- 举报
还没研究Filtered Index.有空看下.
liangCK 2009-11-26
- 打赏
- 举报
[Quote=引用 29 楼 cciebill 的回复:]
学习
[/Quote]
学习
[/Quote]
午夜还在张 2009-11-26
- 打赏
- 举报
学习
FigoZhu 2009-11-26
- 打赏
- 举报
jf
lfywy 2009-11-26
- 打赏
- 举报
不错，收藏~~~
langziqian 2009-11-26
- 打赏
- 举报
Mark
xiaoxiangqing 2009-11-26
- 打赏
- 举报
学习先
ws_hgo 2009-11-26
- 打赏
- 举报
踩踩~
csdyyr 2009-11-26
- 打赏
- 举报
学习
加载更多回复（31）
