扩展 sp_helpindex, 增加 INCLUDE 和筛选索引的筛选条件

SQL Server的系统存储过程似乎没有被重视，新版本的一些特性似乎没有在系统存储过程中体现出来，着实是一件比较郁闷的事。

下面的这个存储过程是对sp_helpindex的扩展，扩展增加两个索引的新特性信息：包含列和列筛选。使用方法和 sp_helpindex 一样，感兴趣的可以试一下。

USE master;

GO
/*-- sp_helpindex 扩展
--------------------------------------------
功能:
扩展sp_helpindex 的信息, 在index_description 中增加filter 信息
在index_keys 中增加include 列信息

--------------------------------------------
-- 应用示例:
USE tempdb;
CREATE TABLE dbo.tb(
id int PRIMARY KEY,
col int UNIQUE,
col1 int,
col2 int
);
CREATE INDEX IX_col
ON dbo.tb(
col1, col2
)
INCLUDE(
id, col
)
WHERE id < 999
;
GO

EXEC sp_helpindex2 'dbo.tb';
GO
DROP TABLE dbo.tb
/*--结果:
index_name index_description index_keys
--------------------------- -------------------------------------------------------- ------------------------------
PK__tb__3213E83F3AD6B8E2 clustered, unique, primary key located on PRIMARY id
UQ__tb__D8360F723DB3258D nonclustered, unique, unique key located on PRIMARY col
IX_col nonclustered located on PRIMARY, filter={([id]<(999))} col1,col2 || include: id,col
--*/
-- 邹建2013.02 --*/
CREATE PROCEDURE dbo.sp_helpindex2
@objname nvarchar(776) -- the table to check for indexes
AS
-- PRELIM
SET NOCOUNT ON;

DECLARE
@objid int, -- the object id of the table
@dbname sysname
;

-- Check to see that the object names are local to the current database.
SET @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.
SET @objid = OBJECT_ID(@objname)
IF @objid IS NULL
BEGIN
RAISERROR(15009,-1,-1,@objname,@dbname);
RETURN (1);
END;

-- IF NO INDEX, QUIT
IF NOT EXISTS(
SELECT *
FROM sys.indexes
WHERE type IN(1, 2, 5, 6)
AND object_id = @objid
)
BEGIN;
RAISERROR(15472,-1,-1,@objname); -- Object does not have any indexes.
RETURN (0);
END;

-- got index information
WITH
IX AS(
SELECT
I.object_id, 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,
I.has_filter, I.filter_definition,
is_columnstore
= CASE
WHEN I.type IN( 5, 6) THEN 1
ELSE 0
END,
auto_created
= CASE
WHEN I.type IN( 5, 6) THEN 0
ELSE S.auto_created
END,
no_recompute
= CASE
WHEN I.type IN( 5, 6) THEN 0
ELSE S.no_recompute
END,
group_name
= CASE
WHEN SERVERPROPERTY('EngineEdition') = 5 THEN NULL
ELSE (
SELECT TOP(1)
name
FROM sys.data_spaces
WHERE data_space_id = I.data_space_id
)
END
FROM sys.indexes I
LEFT JOIN sys.stats S
ON I.object_id = S.object_id
AND I.index_id = S.stats_id
WHERE I.type IN(1, 2, 5, 6)
AND I.object_id = @objid
)
SELECT
index_name = IX.name,
index_description
= CONVERT(nvarchar(max),
CASE WHEN IX.index_id = 1 THEN 'clustered' ELSE 'nonclustered' END
+ CASE WHEN IX.ignore_dup_key <>0 THEN ', ignore duplicate keys' ELSE '' END
+ CASE WHEN IX.is_unique <>0 THEN ', unique' ELSE '' END
+ CASE WHEN IX.is_hypothetical <>0 THEN ', hypothetical' ELSE '' END
+ CASE WHEN IX.is_primary_key <>0 THEN ', primary key' ELSE '' END
+ CASE WHEN IX.is_unique_constraint <>0 THEN ', unique key' ELSE '' END
+ CASE WHEN IX.is_columnstore <>0 THEN ', columnstore' ELSE '' END
+ CASE WHEN IX.auto_created <>0 THEN ', auto create' ELSE '' END
+ CASE WHEN IX.no_recompute <>0 THEN ', stats no recompute' ELSE '' END
+ ' located on ' + ISNULL(IX.group_name, '')
+ CASE WHEN IX.has_filter = 1 THEN N', filter={' + IX.filter_definition + N'}' ELSE N'' END
),
index_keys
= COL_kEYS.value.value('/', 'nvarchar(max)')
+ ISNULL(
N' || include: '
+ COL_INCLUDES.value.value('/', 'nvarchar(max)'),
N''
)
FROM IX
CROSS APPLY(
SELECT
CASE
WHEN ROW_NUMBER() OVER( ORDER BY IX_COL.key_ordinal ) = 1
THEN N''
ELSE N', '
END
+ COL.name
+ CASE WHEN IX_COL.is_descending_key = 1 THEN N'(-)' ELSE N'' END
FROM sys.index_columns IX_COL
INNER JOIN sys.columns COL
ON COL.object_id = IX_COL.object_id
AND COL.column_id = IX_COL.column_id
WHERE IX_COL.object_id = IX.object_id
AND IX_COL.index_id = IX.index_id
AND IX_COL.is_included_column = 0
ORDER BY IX_COL.key_ordinal
FOR XML PATH(''), TYPE
)COL_kEYS(
value)
CROSS APPLY(
SELECT
CASE
WHEN ROW_NUMBER() OVER( ORDER BY IX_COL.key_ordinal ) = 1
THEN N''
ELSE N', '
END
+ COL.name
FROM sys.index_columns IX_COL
INNER JOIN sys.columns COL
ON COL.object_id = IX_COL.object_id
AND COL.column_id = IX_COL.column_id
WHERE IX_COL.object_id = IX.object_id
AND IX_COL.index_id = IX.index_id
AND IX_COL.is_included_column = 1
ORDER BY IX_COL.key_ordinal
FOR XML PATH(''), TYPE
)COL_INCLUDES(
value)
;

RETURN (0); -- sp_helpindex
GO
-- mark system object
EXEC sys.sp_MS_marksystemobject'dbo.sp_helpindex2';
GO