CREATE PROCEDURE [dbo].[usp_Process_NCIndexes_On_Table]
--EXEC dbo.usp_Process_NCIndexes_On_Table @Table_Name = 'sysdiagrams', @Schema_Name = 'dbo', @Action_Type = 'disable'
@Action_Type AS NVARCHAR(MAX),
@Table_Name AS NVARCHAR(MAX),
@Schema_Name AS NVARCHAR(10)

AS
BEGIN
-- ###########################
IF NOT (LOWER(@Action_Type) = 'enable' OR LOWER(@Action_Type) = 'disable')
	BEGIN
		SELECT 'Action type provided has incorrect value. Please call the procedure either with ''enable'' or ''disable'' parameter'
		GOTO BRANCH_END
	END
-- ###########################
	
DECLARE @SQLCMD AS NVARCHAR(MAX)
DECLARE @Final_SQL AS NVARCHAR(MAX)
DECLARE @Index_Name AS NVARCHAR(2000)

--DECLARE @Table_Name AS NVARCHAR(100)
--SET @Table_Name = 'sysdiagrams'
--DECLARE @Schema_Name AS NVARCHAR(10)
--SET @Schema_Name = 'dbo'

SET @SQLCMD = 'DECLARE indexes_cursor CURSOR READ_ONLY FOR 
SELECT i.name AS [Index Name]
FROM sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
WHERE i.type = 2 --NC INDEX TYPE
AND tbl.name = ''' + @Table_Name + ''' AND schema_name(schema_id) = ''' + @Schema_Name + ''''

EXEC sp_executesql @SQLCMD

IF @Action_Type = 'enable' 
	BEGIN
		GOTO BRANCH_ENABLE
	END
ELSE
	BEGIN
		GOTO BRANCH_DISABLE
	END
	
-- ############
BRANCH_DISABLE:
OPEN indexes_cursor
FETCH NEXT FROM indexes_cursor INTO @Index_Name

WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @Final_SQL = 'ALTER INDEX [' + @Index_Name + '] ON ' + '[' + @Schema_Name + '].[' + @Table_Name + ']' + ' DISABLE'
		--EXEC @Final_SQL
		PRINT @Final_SQL
			FETCH NEXT FROM indexes_cursor INTO @Index_Name
	END

CLOSE indexes_cursor
DEALLOCATE indexes_cursor
GOTO BRANCH_END

-- ############
BRANCH_ENABLE:
OPEN indexes_cursor
FETCH NEXT FROM indexes_cursor INTO @Index_Name

WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @Final_SQL = 'ALTER INDEX [' + @Index_Name + '] ON ' + '[' + @Schema_Name + '].[' + @Table_Name + ']' + ' REBUILD WITH (FILLFACTOR=100, PAD_INDEX=ON)'
		--EXEC @Final_SQL
		PRINT @Final_SQL
			FETCH NEXT FROM indexes_cursor INTO @Index_Name
	END

CLOSE indexes_cursor
DEALLOCATE indexes_cursor
GOTO BRANCH_END

BRANCH_END:
	PRINT 'Done'
END
GO

USE master


DECLARE @DBName AS NVARCHAR(MAX)
SET @DBName = 'NPM_DB'

SELECT TOP 20
CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure, 
'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) + ' ON ' + mid.statement + ' (' + ISNULL (mid.equality_columns,'') + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement, 
object_name(mid.[object_id],
mid.database_id) as objectname, 
migs.unique_compiles,
migs.user_seeks,
migs.user_scans,
migs.last_user_seek,
migs.last_user_scan,
migs.avg_user_impact

FROM sys.dm_db_missing_index_groups mig 
INNER JOIN sys.dm_db_missing_index_group_stats migs 
ON migs.group_handle = mig.index_group_handle 
INNER JOIN sys.dm_db_missing_index_details mid 
ON mig.index_handle = mid.index_handle 
 
WHERE CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10 
AND mid.database_id = DB_ID(@DBName)
--AND mid.statement LIKE '%vendor%'
--AND object_name(mid.[object_id], mid.database_id) not like '%2019%'


order by 5 desc

--ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC