SQL-запрос для отображения таблиц, их индексов и использования индексов

--use NavisionDatabase

IF OBJECT_ID ('z_IUQ2_Temp_Index_Keys', 'U') IS NOT NULL
    DROP TABLE z_IUQ2_Temp_Index_Keys;

-- Generate list of indexes with key list
CREATE TABLE z_IUQ2_Temp_Index_Keys(
            [l1] [INT] NOT NULL,
            [F_Obj_ID] [INT] NOT NULL,
            [F_Schema_Name] [nvarchar] (128) NULL,
            [F_Table_Name] [nvarchar] (128) NOT NULL,
            [F_Row_Count] [INT] NULL,
            [F_Reserved] [INT] NULL,
            [F_Data] [INT] NULL,
            [F_Index_Size] [INT] NULL,
            [F_UnUsed] [INT] NULL,
            [F_Index_Name] [nvarchar] (128) NOT NULL,
            [F_Index_ID] [INT] NOT NULL,
            [F_Column_Name] [nvarchar] (128) NOT NULL,
            [F_User_Updates] [INT] NULL,
            [F_User_Reads] [INT] NULL,
            [F_Last_Used] [datetime] NULL,
            [F_Index_Type] [nvarchar] (128) NOT NULL,
            [F_Index_Column_ID] [INT] NOT NULL,
            [F_Last_Seek] [datetime] NULL,
            [F_Last_Scan] [datetime] NULL,
            [F_Last_Lookup] [datetime] NULL,
            [Index_Key_List] [nvarchar] (MAX) NULL
            )

GO

CREATE NONCLUSTERED INDEX [Object_ID_Index] ON [dbo].[z_IUQ2_Temp_Index_Keys]
([F_Obj_ID] ASC)
GO

CREATE NONCLUSTERED INDEX [Index_ID_Index] ON [dbo].[z_IUQ2_Temp_Index_Keys]
([F_Index_ID] ASC)
GO

CREATE NONCLUSTERED INDEX [RowCount_ID_Index] ON [dbo].[z_IUQ2_Temp_Index_Keys]
([F_Row_Count] ASC)
GO

INSERT INTO z_IUQ2_Temp_Index_Keys
SELECT (ROW_NUMBER() OVER(ORDER BY a3.name, a2.name))%2 AS l1,
a1.object_id, ---
a3.name AS [schemaname],
a2.name AS [tablename],
a1.ROWS AS ROW_COUNT,
(a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,
a1.DATA * 8 AS DATA,
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.DATA THEN (a1.used + ISNULL(a4.used,0)) - a1.DATA ELSE 0 END) * 8 AS index_size,
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused,
-- Index Description
SI.name,
SI.Index_ID,
index_col(object_name(SIC.object_id),SIC.index_id,SIC.Index_Column_ID),
-- Index Stats
US.user_updates,
US.user_seeks + US.user_scans + US.user_lookups User_Reads,
CASE
    WHEN (ISNULL(US.last_user_seek,'00:00:00.000') >= ISNULL(US.last_user_scan,'00:00:00.000')) AND (ISNULL(US.last_user_seek,'00:00:00.000') >= ISNULL(US.last_user_lookup,'00:00:00.000')) THEN US.last_user_seek
    WHEN (ISNULL(US.last_user_scan,'00:00:00.000') >= ISNULL(US.last_user_seek,'00:00:00.000')) AND (ISNULL(US.last_user_scan,'00:00:00.000') >= ISNULL(US.last_user_lookup,'00:00:00.000')) THEN US.last_user_scan
    ELSE US.last_user_lookup
END AS Last_Used_For_Reads,
SI.type_desc,
SIC.index_column_id,
US.last_user_seek,
US.last_user_scan,
US.last_user_lookup,
''

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 a1

        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 = a1.object_id)

        INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )

        INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)

        INNER JOIN sys.indexes SI ON (SI.object_id = a1."object_id") ---

        INNER JOIN sys.index_columns SIC ON (SIC.object_id = SI.object_id AND SIC.index_id = SI.index_id)

        LEFT OUTER JOIN sys.dm_db_index_usage_stats US ON (US.object_id = SI.object_id AND US.index_id = SI.index_id)

WHERE a2.TYPE <> N'S' AND a2.TYPE <> N'IT'
ORDER BY ROW_COUNT DESC
GO

-- Populate key string
DECLARE IndexCursor cursor FOR
    SELECT F_Obj_ID, F_Index_ID FROM z_IUQ2_Temp_Index_Keys
    FOR UPDATE OF Index_Key_List

DECLARE @objID INT
DECLARE @IndID INT
DECLARE @KeyString VARCHAR(MAX)

SET @KeyString = NULL

OPEN IndexCursor

SET NOCOUNT ON

fetch NEXT FROM IndexCursor INTO @ObjID, @IndID

while @@fetch_status = 0 BEGIN
    SET @KeyString = ''
    SELECT @KeyString = COALESCE(@KeyString,'') + F_Column_Name + ', '
    FROM z_IUQ2_Temp_Index_Keys
    WHERE F_Obj_ID = @ObjID AND F_Index_ID = @IndID
    ORDER BY F_Index_ID, F_Index_Column_ID

    SET @KeyString = LEFT(@KeyString,LEN(@KeyString) -2)

    UPDATE z_IUQ2_Temp_Index_Keys
    SET Index_Key_List = @KeyString
        WHERE CURRENT OF IndexCursor

    fetch NEXT FROM IndexCursor INTO @ObjID, @IndID

END;

close IndexCursor
deallocate IndexCursor

GO

-- clean up table to one line per index
DELETE FROM z_IUQ2_Temp_Index_Keys
WHERE [F_Index_Column_ID] > 1

GO

SELECT  [F_Table_Name] TableName,
        [F_Row_Count] No_Of_Records,
        [F_Data] Data_Size,
        [F_Index_Size] Index_Size,
        [F_UnUsed] UnUsed_Space,
        [F_Index_Name] Index_Name,
        [F_User_Updates] Index_Updates,
        [F_User_Reads] Index_Reads,
        [F_Last_Used] Index_Last_Used,
        [F_Index_Type] Index_Type,
        [Index_Key_List] Index_Fields
FROM z_IUQ2_Temp_Index_Keys
ORDER BY F_Row_Count DESC, F_Table_Name, [F_Index_ID]