/*
Server:		ssunb008vm01.sunstore.ch
Format:		GCM 
Business:	TPPHAR
type:		DEVE
Version:	23.1.10016.00066

01.11.2022, TSC
*/
USE Arizona;
BEGIN TRANSACTION;
SET XACT_ABORT ON;
SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#chkRes') IS NOT NULL BEGIN;
    DROP TABLE #chkRes;
END
DECLARE @ref_schema VARCHAR(50) = 'dbo';
DECLARE @ref_table VARCHAR(100) = 'entry';

CREATE TABLE #chkRes (
    tbl_name NVARCHAR(128) NOT NULL 
   ,[schema_name] VARCHAR(50) NOT NULL 
   ,[type] CHAR(2) NULL 
   ,index_name NVARCHAR(128) NULL 
   ,[index_id] INT NOT NULL 
   ,[Reads] BIGINT NOT NULL 
   ,[Writes] BIGINT NOT NULL
   ,[delta] BIGINT NOT NULL
   ,[index_type] NVARCHAR(60) NULL
   ,[FillFactor] TINYINT NULL
   ,[has_filter] BIT NOT NULL 
   ,[filter_definition] NVARCHAR(MAX) NULL
   ,[last_user_scan] DATETIME NULL
   ,[last_user_lookup] DATETIME NULL
   ,[last_user_seek] DATETIME NULL
   ,[IndexColumns] NVARCHAR(MAX) NULL
   ,[IncludedColumns] NVARCHAR(MAX) NULL
   ,[primary_table] NVARCHAR(500) NULL
   ,[pk_column_name] NVARCHAR(128) NULL
   ,[fk_constraint_name] NVARCHAR(128) NULL
   ,comp_drop_cmd AS 'DROP INDEX ' + index_name + ' ON ' + [schema_name] + '.' + tbl_name + ';'
);

;WITH ColInfo
AS (
    SELECT
        o.name                 AS TblName
       ,s.name + '.' + o.name  AS SchemaTbl
       ,i.name                 AS IndexName
       ,i.is_primary_key       AS IsPrimaryKey
       ,i.is_unique_constraint AS IsUniqueConstraint
       ,i.is_unique            AS IsUnique
       ,c.name                 AS ColName
       ,c.is_computed          AS IsComputedCol
       ,ic.is_included_column  AS IsIncludedCol
       ,ic.key_ordinal
       ,i.filter_definition    AS FilterDefinition
    FROM sys.objects                 o
        INNER JOIN sys.schemas       s
            ON o.schema_id  = s.schema_id
        INNER JOIN sys.columns       c
            ON o.object_id  = c.object_id
        INNER JOIN sys.indexes       i
            ON c.object_id  = i.object_id
        INNER JOIN sys.index_columns ic
            ON i.index_id   = ic.index_id
            AND o.object_id = ic.object_id
            AND c.column_id = ic.column_id
)


--- Index Read/Write stats (all tables in current DB) ordered by Reads  (Query 62) (Overall Index Usage - Reads)
INSERT INTO #chkRes (
    tbl_name
   ,[schema_name]
   ,[type]
   ,index_name
   ,index_id
   ,Reads
   ,Writes
   ,delta
   ,index_type
   ,[FillFactor]
   ,has_filter
   ,filter_definition
   ,last_user_scan
   ,last_user_lookup
   ,last_user_seek
   ,IndexColumns
   ,IncludedColumns
   ,primary_table
   ,pk_column_name
   ,fk_constraint_name
)
SELECT
    o.name                                 AS [tbl_Name]
   ,SCHEMA_NAME(o.schema_id)			   AS [schema_name]
   ,o.type
   ,i.name                                 AS [index_name]
   ,i.index_id
   ,user_seeks + user_scans + user_lookups AS [Reads]
   ,s.user_updates                         AS [Writes]
   ,CASE
        WHEN s.user_updates > user_seeks + user_scans + user_lookups THEN
            s.user_updates - (user_seeks + user_scans + user_lookups)
        ELSE (user_seeks + user_scans + user_lookups) - s.user_updates
    END                                    AS delta
   ,i.type_desc                            AS [index_type]
   ,i.fill_factor                          AS [fill_factor]
   ,i.has_filter
   ,i.filter_definition
   ,s.last_user_scan
   ,s.last_user_lookup
   ,s.last_user_seek
   ,idx_cols.IndexColumns
   ,idx_cols.IncludedColumns
   ,fk.primary_table
   ,fk.pk_column_name
   ,fk.fk_constraint_name
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
    INNER JOIN sys.indexes       AS i WITH (NOLOCK)
        ON s.object_id = i.object_id
    INNER JOIN sys.objects       o WITH (NOLOCK)
        ON s.object_id = o.object_id
    OUTER APPLY (
    SELECT DISTINCT
           x.TblName
          ,x.IndexName
          ,x.IsPrimaryKey
          ,x.IsUniqueConstraint
          ,x.IsUnique
          --,size.IndexSizeKB
          ,c.IndexColumns
          ,ci.IncludedColumns
          ,cc.ComputedColumns
          ,x.FilterDefinition
    FROM ColInfo x
        CROSS APPLY (
        SELECT STUFF(sq.strXML, 1, 2, '') AS IndexColumns
        FROM (
            SELECT ', ' + ISNULL(x2.ColName, '')
            FROM ColInfo x2
            WHERE x.TblName      = x2.TblName
            AND x.IndexName      = x2.IndexName
            AND x2.IsIncludedCol = 0
            ORDER BY x2.key_ordinal
            FOR XML PATH('')
        ) sq(strXML)
    )            c
        OUTER APPLY (
        SELECT STUFF(sq.strXML, 1, 2, '') AS IncludedColumns
        FROM (
            SELECT ', ' + ISNULL(x2.ColName, '')
            FROM ColInfo x2
            WHERE x.TblName      = x2.TblName
            AND x.IndexName      = x2.IndexName
            AND x2.IsIncludedCol = 1
            ORDER BY x2.key_ordinal
            FOR XML PATH('')
        ) sq(strXML)
    ) ci
        OUTER APPLY (
        SELECT STUFF(sq.strXML, 1, 2, '') AS ComputedColumns
        FROM (
            SELECT ', ' + ISNULL(x2.ColName, '')
            FROM ColInfo x2
            WHERE x.TblName      = x2.TblName
            AND x.IndexName      = x2.IndexName
            AND x2.IsComputedCol = 1
            ORDER BY x2.key_ordinal
            FOR XML PATH('')
        ) sq(strXML)
    ) cc
    WHERE x.TblName = o.name
    AND x.IndexName = i.name
)                                idx_cols
    LEFT OUTER JOIN (
        SELECT
            SCHEMA_NAME(tab.schema_id) + '.' + tab.name       AS [table]
           ,col.column_id
           ,col.name                                          AS column_name
           ,CASE
                WHEN fk.object_id IS NOT NULL THEN '>-'
                ELSE NULL
            END                                               AS rel
           ,SCHEMA_NAME(pk_tab.schema_id) + '.' + pk_tab.name AS primary_table
           ,pk_col.name                                       AS pk_column_name
           ,fk_cols.constraint_column_id                      AS NO
           ,fk.name                                           AS fk_constraint_name
           ,tab.name                                          AS tbl_name
        FROM sys.tables                             tab
            INNER JOIN sys.columns                  col
                ON col.object_id             = tab.object_id
            LEFT OUTER JOIN sys.foreign_key_columns fk_cols
                ON fk_cols.parent_object_id  = tab.object_id
                AND fk_cols.parent_column_id = col.column_id
            LEFT OUTER JOIN sys.foreign_keys        fk
                ON fk.object_id              = fk_cols.constraint_object_id
            LEFT OUTER JOIN sys.tables              pk_tab
                ON pk_tab.object_id          = fk_cols.referenced_object_id
            LEFT OUTER JOIN sys.columns             pk_col
                ON pk_col.column_id          = fk_cols.referenced_column_id
                AND pk_col.object_id         = fk_cols.referenced_object_id
        WHERE fk.object_id IS NOT NULL
    ) fk
        ON fk.tbl_name = o.name
        AND idx_cols.IndexColumns LIKE '%' + fk.column_name + '%'
WHERE o.type                 = 'U' -- user table
AND i.index_id               = s.index_id
AND s.database_id            = DB_ID()
AND o.name                   = @ref_table
AND SCHEMA_NAME(o.schema_id) = @ref_schema
ORDER BY delta DESC
--ORDER BY user_seeks + user_scans + user_lookups DESC  -- Order by reads
--ORDER BY s.user_updates DESC OPTION   				 -- Order by writes
OPTION (RECOMPILE);



/* list back data */
SELECT cr.tbl_name
      ,cr.[schema_name]
      ,cr.[type]
      ,cr.index_name
      ,cr.index_id
      ,cr.Reads
      ,cr.Writes
      ,cr.delta
      ,cr.index_type
      ,cr.[FillFactor]
      ,cr.has_filter
      ,cr.filter_definition
      ,cr.last_user_scan
      ,cr.last_user_lookup
      ,cr.last_user_seek
      ,cr.IndexColumns
      ,cr.IncludedColumns
      ,cr.primary_table
      ,cr.pk_column_name
      ,cr.fk_constraint_name
FROM #chkRes cr
ORDER BY IndexColumns
;

ROLLBACK TRANSACTION 

RETURN


--/* Check if index with FK are in the list, alert if it's the case */
--IF EXISTS(
--	SELECT 1
--	FROM #chkRes cr
--	WHERE cr.Reads = 0 --not used
--	AND cr.Writes  > 100 --but maintained
--	AND cr.fk_constraint_name IS NOT NULL --is part of a FK
--) BEGIN
--	SELECT
--		'This index is tied to a column part of a FK, check if dropping is to be done.' AS msg
--	   ,cr.[schema_name]
--	   ,cr.tbl_name
--	   ,cr.index_name
--	   ,cr.Reads
--	   ,cr.Writes
--	   ,cr.primary_table
--	   ,cr.pk_column_name
--	   ,cr.fk_constraint_name
--	FROM #chkRes cr
--	WHERE cr.Reads = 0 --not used
--	AND cr.Writes  > 100 --but maintained
--	AND cr.fk_constraint_name IS NOT NULL --is part of a FK
--	AND cr.index_type = 'NONCLUSTERED'
--END 


--/* declare variables */
--DECLARE
--    @table_name  VARCHAR(MAX)
--   ,@schema_name VARCHAR(20)
--   ,@index_name  VARCHAR(500)
--   ,@drop_cmd	 VARCHAR(MAX)
--   ;

--DECLARE dropIdx CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY FOR
--	SELECT
--		cr.tbl_name
--	   ,cr.[schema_name]
--	   ,cr.index_name
--	   ,cr.comp_drop_cmd
--	FROM #chkRes cr
--	WHERE cr.Reads = 0 --not used on select
--	AND cr.Writes  > 100 --but maintained on insert / update
--	AND cr.fk_constraint_name IS NULL --not part of a FK
--	AND cr.index_type = 'NONCLUSTERED'	--ignore clustered & heaps
--	;

--OPEN dropIdx;

--FETCH NEXT FROM dropIdx
--INTO
--    @table_name
--   ,@schema_name
--   ,@index_name
--   ,@drop_cmd
--   ;

--WHILE @@FETCH_STATUS = 0 BEGIN
--    PRINT @drop_cmd;--'Dropping index ' + @index_name + ' on ' + @schema_name + '.' + @table_name;
--	--EXEC(@drop_cmd);
--    FETCH NEXT FROM dropIdx
--    INTO
--        @table_name
--       ,@schema_name
--       ,@index_name
--	   ,@drop_cmd
--       ;
--END

--CLOSE dropIdx;
--DEALLOCATE dropIdx;


--SELECT cr.tbl_name
--      ,cr.[schema_name]
--      ,cr.index_name
--      ,cr.Reads
--      ,cr.Writes
--      ,cr.delta
--      ,cr.index_type
--      ,cr.last_user_scan
--      ,cr.last_user_lookup
--      ,cr.last_user_seek
--      ,cr.IndexColumns
--      ,cr.IncludedColumns
--      ,cr.primary_table
--      ,cr.pk_column_name
--      ,cr.fk_constraint_name
--FROM #chkRes cr
--ORDER BY cr.delta DESC 
--;

--ROLLBACK TRANSACTION;