sp_helpindex על סטרואידים

USE [master];

SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON;

GO

IF  EXISTS (SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID(N'[dbo].[sp_helpindex2]') AND type IN (N'P', N'PC'))

    DROP PROCEDURE [dbo].[sp_helpindex2];

GO

CREATE PROCEDURE dbo.sp_helpindex2

(

     @Table sysname

    ,@IndexExtendedInfo bit = 0

    ,@ColumnsInfo bit = 1

)

/*

    Yaniv Etrogi 20100328

    sp_helpindex2 adds the included columns information that is not provided by the original sp_helpindex.

    Send your comments to mailto:yaniv.etrogi@gmail.com or leave your comments at 

    http://blogs.microsoft.co.il/blogs/yaniv_etrogi/

    http://www.sqlserverutilities.com    

*/

AS

SET NOCOUNT ON;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- Index information

SELECT 

     SCHEMA_NAME(o.SCHEMA_ID) AS [Schema]

    ,o.name AS [Table]

    ,i.name AS [Index]

    ,i.object_id

    ,i.data_space_id

    ,i.index_id

    ,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.[allow_row_locks] 

  ,i.[allow_page_locks] 

    ,i.is_disabled 

    ,i.fill_factor 

    ,i.is_padded 

    ,LEFT(list, ISNULL(splitter-1,LEN(list))) AS [Columns]

    ,SUBSTRING(list, indCol.splitter +1, 100) AS Included_Columns 

    ,COUNT(1) OVER (PARTITION BY o.[object_id]) AS Num_Indexes

INTO dbo.#Indexes

FROM sys.indexes i

INNER JOIN sys.objects o ON i.[object_id] = o.[object_id] 

INNER JOIN  sys.stats s ON i.[object_id] = s.[object_id] AND i.index_id = s.stats_id

CROSS APPLY (SELECT NULLIF(CHARINDEX('|',indexCols.list),0) splitter , list

              FROM (SELECT CAST((SELECT CASE WHEN sc.is_included_column = 1 AND sc.ColPos = 1 THEN '|' ELSE '' END + CASE WHEN sc.ColPos  > 1 THEN ', ' ELSE '' END + name

                             FROM (SELECT 

                                                                             sc.is_included_column

                                                                            ,index_column_id, name

                                      ,ROW_NUMBER() OVER (PARTITION BY sc.is_included_column ORDER BY sc.index_column_id) ColPos

                                    FROM sys.index_columns  sc

                                    INNER JOIN sys.columns c ON sc.[object_id] = c.[object_id] AND sc.column_id = c.column_id

                                    WHERE sc.index_id = i.index_id AND sc.[object_id] = i.[object_id] ) sc

                                ORDER BY sc.is_included_column, ColPos

                      FOR XML PATH (''), TYPE) AS VARCHAR(MAX)) list)indexCols ) indCol

WHERE o.name = @Table;

-- Table information

SELECT

    (row_number() OVER(ORDER BY t3.name, t2.name))%2 AS l1

    ,DB_NAME() AS [Database]

    ,t3.name AS [schemaname]

    ,t2.name AS [tablename]

    ,t1.rows AS row_count

    ,((t1.reserved + ISNULL(a4.reserved,0))*  / 1024 AS reserved_MB 

    ,(t1.data *  / 1024 AS data_MB

    ,((CASE WHEN (t1.used + ISNULL(a4.used,0)) > t1.data THEN (t1.used + ISNULL(a4.used,0)) - t1.data ELSE 0 END) *  /1024 AS index_size_MB

    ,((CASE WHEN (t1.reserved + ISNULL(a4.reserved,0)) > t1.used THEN (t1.reserved + ISNULL(a4.reserved,0)) - t1.used ELSE 0 END) * 8)/1024 AS unused_MB

INTO dbo.#Table

FROM

 (SELECT 

     ps.object_id

    ,SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows]

    ,SUM (ps.reserved_page_count) AS reserved

    ,SUM (CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END) AS data

    ,SUM (ps.used_page_count) AS used

  FROM sys.dm_db_partition_stats ps

  GROUP BY ps.[object_id]) AS t1

LEFT OUTER JOIN 

 (SELECT 

       it.parent_id

      ,SUM(ps.reserved_page_count) AS reserved

      ,SUM(ps.used_page_count) AS used

  FROM sys.dm_db_partition_stats ps

  INNER JOIN sys.internal_tables it ON (it.[object_id] = ps.[object_id]) WHERE it.internal_type IN (202,204)

  GROUP BY it.parent_id) AS a4 ON (a4.parent_id = t1.[object_id])

INNER JOIN sys.all_objects t2  ON ( t1.[object_id] = t2.[object_id]) 

INNER JOIN sys.schemas t3 ON (t2.[schema_id] = t3.[schema_id])

WHERE t2.[type] <> 'S' AND t2.[type] <> 'IT' 

AND t2.name = @Table;

-- Additional index information if requested.

IF (@IndexExtendedInfo = 1)

BEGIN;

    SELECT * 

    INTO dbo.#physical_stats

    FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(@Table, N'U'), NULL , NULL, 'LIMITED');

    SELECT 

    i.[Index]    

    ,CONVERT(varchar(250), 

                  CASE WHEN i.index_id = 1 THEN 'clustered' ELSE 'nonclustered' END

                + CASE WHEN i.[ignore_dup_key] <> 0 THEN ', ignore duplicate keys' ELSE '' END

                + CASE WHEN i.is_unique <> 0 THEN ', unique' ELSE '' END

                + CASE WHEN i.is_hypothetical <> 0 THEN ', hypothetical' ELSE '' END

                + CASE WHEN i.is_primary_key <> 0 THEN ', primary key' ELSE '' END

                + case when i.is_unique_constraint <> 0 THEN ', unique key' ELSE '' END

                + CASE WHEN i.auto_created <> 0 THEN ', auto create' ELSE '' END

                + CASE WHEN i.no_recompute <> 0 THEN ', stats no recompute' ELSE '' END

                + ' located on ' + d.name ) AS [Description]

    ,i.[Columns] 

    ,i.Included_Columns 

    ,t.row_count            AS Table_Row_Count

    ,t.reserved_MB        AS Table_Reserved_MB

    ,t.data_MB                AS Table_Data_MB

    ,t.index_size_MB    AS Table_Index_Size_MB

    ,s.avg_fragmentation_in_percent 

    ,s.page_count 

    ,s.partition_number 

    ,i.is_disabled 

    ,i.fill_factor 

    ,i.is_padded 

    ,i.[allow_row_locks] 

  ,i.[allow_page_locks] 

FROM dbo.#Indexes i

INNER JOIN dbo.#Table t ON i.[Schema] = t.SchemaName AND i.[Table] = t.[tablename]

INNER JOIN sys.data_spaces d ON d.data_space_id = i.data_space_id 

INNER JOIN dbo.#physical_stats s ON i.[object_id] = s.[object_id]  AND i.index_id = s.index_id

ORDER BY i.[Index];    

END

ELSE BEGIN

SELECT 

    i.[Index]    

    ,CONVERT(varchar(250), 

                  CASE WHEN i.index_id = 1 THEN 'clustered' ELSE 'nonclustered' END

                + CASE WHEN i.[ignore_dup_key] <> 0 THEN ', ignore duplicate keys' ELSE '' END

                + CASE WHEN i.is_unique <> 0 THEN ', unique' ELSE '' END

                + CASE WHEN i.is_hypothetical <> 0 THEN ', hypothetical' ELSE '' END

                + CASE WHEN i.is_primary_key <> 0 THEN ', primary key' ELSE '' END

                + case when i.is_unique_constraint <> 0 THEN ', unique key' ELSE '' END

                + CASE WHEN i.auto_created <> 0 THEN ', auto create' ELSE '' END

                + CASE WHEN i.no_recompute <> 0 THEN ', stats no recompute' ELSE '' END

                + ' located on ' + d.name ) AS [Description]

    ,i.[Columns] 

    ,i.Included_Columns 

    ,t.row_count            AS Table_Row_Count

    ,t.reserved_MB        AS Table_Reserved_MB

    ,t.data_MB                AS Table_Data_MB

    ,t.index_size_MB    AS Table_Index_Size_MB

--,t.unused_MB            AS Table_Unused_MB

    ,i.is_disabled 

    ,i.fill_factor 

    ,i.is_padded 

    ,i.[allow_row_locks] 

  ,i.[allow_page_locks] 

--,i.Num_Indexes

FROM dbo.#Indexes i

INNER JOIN dbo.#Table t ON i.[Schema] = t.SchemaName AND i.[Table] = t.[tablename]

INNER JOIN sys.data_spaces d ON d.data_space_id = i.data_space_id 

ORDER BY i.[Index];

END;

-- Columns information

IF (@ColumnsInfo = 1)

BEGIN;

    EXEC sp_columns @table_name = @Table;

END;

RETURN 0;

GO

USE MASTER; EXEC sp_ms_marksystemobject 'sp_helpindex2';

GO