扩展 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