sp_indexdetails.sql

/*****************************************************************************
Copyright (c) 2018 SQL Workbooks LLC
MIT License, http://www.opensource.org/licenses/mit-license.php
Contact: help@sqlworkbooks.com
*****************************************************************************/

USE master;
GO

CREATE OR ALTER PROCEDURE dbo.sp_indexdetails (
    @databasename sysname = null,
    @tablename sysname,
    @debug tinyint = 0
    )
AS

IF @databasename is null
    SET @databasename= DB_NAME()
ELSE
    IF (SELECT COUNT(*) from sys.databases where name=@databasename) = 0
    BEGIN
        PRINT 'Database not found.'
        RETURN
    END

DECLARE @dsql NVARCHAR(MAX);
SET @dsql = N'
SELECT 
    DB_NAME() AS database_name,
    sc.name + N''.'' + t.name AS table_name,
    (SELECT MAX(user_reads) 
        FROM (VALUES (last_user_seek), (last_user_scan), (last_user_lookup)) AS value(user_reads)) AS last_user_read,
    last_user_update,
    CASE si.index_id WHEN 0 THEN N''/* No create statement (Heap) */''
    ELSE 
        CASE is_primary_key WHEN 1 THEN
            N''ALTER TABLE '' + QUOTENAME(sc.name) + N''.'' + QUOTENAME(t.name) + N'' ADD CONSTRAINT '' + QUOTENAME(si.name) + N'' PRIMARY KEY '' +
                CASE WHEN si.index_id > 1 THEN N''NON'' ELSE N'''' END + N''CLUSTERED ''
            ELSE N''CREATE '' + 
                CASE WHEN si.is_unique = 1 then N''UNIQUE '' ELSE N'''' END +
                CASE WHEN si.index_id > 1 THEN N''NON'' ELSE N'''' END + N''CLUSTERED '' +
                N''INDEX '' + QUOTENAME(si.name) + N'' ON '' + QUOTENAME(sc.name) + N''.'' + QUOTENAME(t.name) + N'' ''
        END +
        /* key def */ N''('' + key_definition + N'')'' +
        /* includes */ CASE WHEN include_definition IS NOT NULL THEN 
            N'' INCLUDE ('' + include_definition + N'')''
            ELSE N''''
        END +
        /* filters */ CASE WHEN filter_definition IS NOT NULL THEN 
            N'' WHERE '' + filter_definition ELSE N''''
        END +
        /* with clause - compression goes here */
        CASE WHEN row_compression_partition_list IS NOT NULL OR page_compression_partition_list IS NOT NULL 
            THEN N'' WITH ('' +
                CASE WHEN row_compression_partition_list IS NOT NULL THEN
                    N''DATA_COMPRESSION = ROW '' + CASE WHEN psc.name IS NULL THEN N'''' ELSE + N'' ON PARTITIONS ('' + row_compression_partition_list + N'')'' END
                ELSE N'''' END +
                CASE WHEN row_compression_partition_list IS NOT NULL AND page_compression_partition_list IS NOT NULL THEN N'', '' ELSE N'''' END +
                CASE WHEN page_compression_partition_list IS NOT NULL THEN
                    N''DATA_COMPRESSION = PAGE '' + CASE WHEN psc.name IS NULL THEN N'''' ELSE + N'' ON PARTITIONS ('' + page_compression_partition_list + N'')'' END
                ELSE N'''' END
            + N'')''
            ELSE N''''
        END +
        /* ON where? filegroup? partition scheme? */
        '' ON '' + CASE WHEN psc.name is null 
            THEN ISNULL(QUOTENAME(fg.name),N'''')
            ELSE psc.name + N'' ('' + partitioning_column.column_name + N'')'' 
            END
        + N'';''
    END AS index_create_statement,
    si.index_id,
    si.name AS index_name,
    partition_sums.in_row_data_page_count,
    partition_sums.reserved_in_row_GB,
    partition_sums.reserved_LOB_GB,
    partition_sums.row_count,
    stat.user_seeks,
    stat.user_scans,
    stat.user_lookups,
    user_updates AS queries_that_modified,
    partition_sums.partition_count,
    si.allow_page_locks,
    si.allow_row_locks,
    si.is_hypothetical,
    si.has_filter,
    si.fill_factor,
    si.is_unique,
    ISNULL(pf.name, ''/* Not partitioned */'') AS partition_function,
    ISNULL(psc.name, fg.name) AS partition_scheme_or_filegroup,
    t.create_date AS table_created_date,
    t.modify_date AS table_modify_date';

SET @dsql = @dsql + N'
FROM ' + quotename(@databasename) +  N'.sys.indexes AS si
JOIN ' + quotename(@databasename) +  N'.sys.tables AS t ON si.object_id=t.object_id
JOIN ' + quotename(@databasename) +  N'.sys.schemas AS sc ON t.schema_id=sc.schema_id
LEFT JOIN ' + quotename(@databasename) +  N'.sys.dm_db_index_usage_stats AS stat ON 
    stat.database_id = DB_ID() 
    and si.object_id=stat.object_id 
    and si.index_id=stat.index_id
LEFT JOIN ' + quotename(@databasename) +  N'.sys.partition_schemes AS psc ON si.data_space_id=psc.data_space_id
LEFT JOIN ' + quotename(@databasename) +  N'.sys.partition_functions AS pf ON psc.function_id=pf.function_id
LEFT JOIN ' + quotename(@databasename) +  N'.sys.filegroups AS fg ON si.data_space_id=fg.data_space_id
/* Key list */ OUTER APPLY ( SELECT STUFF (
    (SELECT N'', '' + QUOTENAME(c.name) +
        CASE ic.is_descending_key WHEN 1 then N'' DESC'' ELSE N'''' END
    FROM ' + quotename(@databasename) +  N'.sys.index_columns AS ic 
    JOIN ' + quotename(@databasename) +  N'.sys.columns AS c ON 
        ic.column_id=c.column_id  
        and ic.object_id=c.object_id
    WHERE ic.object_id = si.object_id
        and ic.index_id=si.index_id
        and ic.key_ordinal > 0
    ORDER BY ic.key_ordinal FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)''),1,2,'''')) AS keys ( key_definition )
/* Partitioning Ordinal */ OUTER APPLY (
    SELECT MAX(QUOTENAME(c.name)) AS column_name
    FROM ' + quotename(@databasename) +  N'.sys.index_columns AS ic 
    JOIN ' + quotename(@databasename) +  N'.sys.columns AS c ON 
        ic.column_id=c.column_id  
        and ic.object_id=c.object_id
    WHERE ic.object_id = si.object_id
        and ic.index_id=si.index_id
        and ic.partition_ordinal = 1) AS partitioning_column
/* Include list */ OUTER APPLY ( SELECT STUFF (
    (SELECT N'', '' + QUOTENAME(c.name)
    FROM ' + quotename(@databasename) +  N'.sys.index_columns AS ic 
    JOIN ' + quotename(@databasename) +  N'.sys.columns AS c ON 
        ic.column_id=c.column_id  
        and ic.object_id=c.object_id
    WHERE ic.object_id = si.object_id
        and ic.index_id=si.index_id
        and ic.is_included_column = 1
    ORDER BY c.name FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)''),1,2,'''')) AS includes ( include_definition )
/* Partitions */ OUTER APPLY ( 
    SELECT 
        COUNT(*) AS partition_count,
        SUM(ps.in_row_data_page_count) AS in_row_data_page_count,
        CAST(SUM(ps.in_row_reserved_page_count)*8./1024./1024. AS NUMERIC(32,1)) AS reserved_in_row_GB,
        CAST(SUM(ps.lob_reserved_page_count)*8./1024./1024. AS NUMERIC(32,1)) AS reserved_LOB_GB,
        SUM(ps.row_count) AS row_count
    FROM ' + quotename(@databasename) +  N'.sys.partitions AS p
    JOIN ' + quotename(@databasename) +  N'.sys.dm_db_partition_stats AS ps ON
        p.partition_id=ps.partition_id
    WHERE p.object_id = si.object_id
        and p.index_id=si.index_id
    ) AS partition_sums
/* row compression list by partition */ OUTER APPLY ( SELECT STUFF (
    (SELECT N'', '' + CAST(p.partition_number AS VARCHAR(32))
    FROM ' + quotename(@databasename) +  N'.sys.partitions AS p
    WHERE p.object_id = si.object_id
        and p.index_id=si.index_id
        and p.data_compression = 1
    ORDER BY p.partition_number FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)''),1,2,'''')) AS row_compression_clause ( row_compression_partition_list )
/* data compression list by partition */ OUTER APPLY ( SELECT STUFF (
    (SELECT N'', '' + CAST(p.partition_number AS VARCHAR(32))
    FROM ' + quotename(@databasename) +  N'.sys.partitions AS p
    WHERE p.object_id = si.object_id
        and p.index_id=si.index_id
        and p.data_compression = 2
    ORDER BY p.partition_number FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)''),1,2,'''')) AS page_compression_clause ( page_compression_partition_list )
WHERE 
    si.type IN (0,1,2) /* heap, clustered, nonclustered */
    and t.name=@tablename
'


IF @debug = 1
    PRINT @dsql;

EXEC sp_executesql @stmt = @dsql, @params = N'@tablename sysname', @tablename=@tablename;

GO