/ Published in: SQL
Expand |
Embed | Plain Text
Copy this code and paste it in your HTML
DECLARE @IndexFilegroupName VARCHAR(200) SET @IndexFilegroupName = 'PRIMARY' SELECT avg_total_user_cost, avg_user_impact, --mid.database_id , --mid.object_id , mid.equality_columns , mid.inequality_columns , mid.included_columns , mid.statement AS [TABLE], user_scans, user_seeks, last_user_seek, last_user_scan, 'CREATE NONCLUSTERED INDEX ix_' + OBJECT_NAME(mid.object_id) + '_' + ISNULL(REPLACE(REPLACE(REPLACE(equality_columns,'[',''),']',''),',',''),'') + ISNULL(REPLACE(REPLACE(REPLACE(inequality_columns,'[',''),']',''),',',''),'') + CASE WHEN included_columns IS NOT NULL THEN '_inc' ELSE '' END + ' ON ' + mid.statement + ' (' + REPLACE(ISNULL(equality_columns,'') + ISNULL(inequality_columns,''),'][','],[') + ')' + CASE WHEN included_columns IS NOT NULL THEN ' INCLUDE (' + included_columns + ')' ELSE '' END + ' ON [' + @IndexFilegroupName + '];' AS statement FROM sys.dm_db_missing_index_group_stats AS migs INNER JOIN sys.dm_db_missing_index_groups AS mig ON (migs.group_handle = mig.index_group_handle) INNER JOIN sys.dm_db_missing_index_details AS mid ON (mig.index_handle = mid.index_handle) ORDER BY 1 DESC