Index Statistics (SQL)

index_stats.sql

USE CDR
GO

DECLARE @DatabaseName nvarchar(50) = 'CDR'
DECLARE @SchemaName nvarchar(50) = NULL
DECLARE @ObjectName nvarchar(50) = NULL
DECLARE @DatabaseID smallint = DB_ID(@DatabaseName)

SELECT *
FROM (

    SELECT
        @DatabaseName AS "DatabaseName",
        S.name AS "SchemaName",
        O.name AS "ObjectName",
        O.type_desc AS "ObjectType",
        CONVERT(date,O.modify_date) AS "ObjectModified",
        I.name AS "IndexName",
        I.type_desc AS "IndexType",
        I.is_primary_key AS "IsPrimaryKey",
        I.is_unique AS "IsUnique",
        I.ignore_dup_key AS "IgnoreDupes",
        I.is_disabled AS "IsDisabled",
        I.filter_definition AS "Filter",
        (
            SELECT CONVERT(decimal(18,2), SUM(used_page_count * 8.0) / 1024.0)
            FROM sys.dm_db_partition_stats
            WHERE object_id = I.object_id AND index_id = I.index_id
        ) AS "IndexSize",
        ISNULL(U.user_seeks, 0) AS "UserSeeks",
        ISNULL(U.user_scans, 0) AS "UserScans",
        ISNULL(U.user_lookups, 0) AS "UserLookups",
        ISNULL(U.user_updates, 0) AS "UserUpdates",
        CONVERT(date,U.last_user_seek) AS "LastUserSeek",
        CONVERT(date,U.last_user_scan) AS "LastUserScan",
        CONVERT(date,U.last_user_lookup) AS "LastUserLookup",
        CONVERT(date,U.last_user_update) AS "LastUserUpdate",
        ISNULL(U.system_seeks, 0) AS "SystemSeeks",
        ISNULL(U.system_scans, 0) AS "SystemScans",
        ISNULL(U.system_lookups, 0) AS "SystemLookups",
        ISNULL(U.system_updates, 0) AS "SystemUpdates",
        CONVERT(date,U.last_system_seek) AS "LastSystemSeek",
        CONVERT(date,U.last_system_scan) AS "LastSystemScan",
        CONVERT(date,U.last_system_lookup) AS "LastSystemLookup",
        CONVERT(date,U.last_system_update) AS "LastSystemUpdate"
    FROM sys.objects AS O
        INNER JOIN sys.schemas AS S ON O.schema_id = S.schema_id
        INNER JOIN sys.indexes AS I ON O.object_id = I.object_id
        LEFT OUTER JOIN (
            SELECT * FROM sys.dm_db_index_usage_stats WHERE database_id = @DatabaseID
        ) AS U ON (I.object_id = U.object_id AND I.index_id = U.index_id)
    WHERE O.is_ms_shipped = 0 AND I.index_id <> 0 AND O.name <> 'sysdiagrams'

) AS T1
WHERE 
    SchemaName = ISNULL(@SchemaName, SchemaName) AND
    ObjectName = ISNULL(@ObjectName, ObjectName)
ORDER BY DatabaseName, SchemaName, ObjectName, IsPrimaryKey DESC, IndexName