1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108
|
CREATE FUNCTION [dbo].[fn_Index_CreateIndexName] (@equality_columns NVARCHAR(4000),@Inequality_columns NVARCHAR(4000), @index_handlE INT) RETURNS VARCHAR(128) AS BEGIN DECLARE @IndexName NVARCHAR(MAX)
SET @IndexName = ISNULL(@equality_columns,@Inequality_columns)
SET @IndexName = LTRIM(REPLACE(@IndexName,'[','_'))
SET @IndexName = RTRIM(REPLACE(@IndexName,']','_'))
SET @IndexName = REPLACE(@IndexName,',','')
SET @IndexName = REPLACE(@IndexName,'_ _','_')
IF LEN(@IndexName) > 120 BEGIN
SET @IndexName = SUBSTRING(@IndexName,0,120)
END
SET @IndexName = @IndexName + CAST(@index_handlE AS NVARCHAR(15)) RETURN @IndexName END
GO
CREATE VIEW [dbo].[vw_Index_MissingIndex]
AS SELECT '[' + d.name + ']' as DBName, [dbo].[fn_Index_CreateIndexName](mid.equality_columns,mid.Inequality_columns,mid.index_handlE) AS ID, REPLACE(mid.equality_columns,',',' ASC,') AS equality_columns, REPLACE(mid.Inequality_columns,',',' ASC,') AS Inequality_columns, mid.Included_columns, mid.[statement] FROM sys.dm_db_missing_index_details mid INNER JOIN sys.databases d on d.database_id = mid.database_id
GO
CREATE PROCEDURE [dbo].[usp_Index_MissingIndexCreationStatements] AS
DECLARE @IndexCreationPlaceholder_Start AS NVARCHAR(MAX) DECLARE @IndexCreationPlaceholder_End AS NVARCHAR(MAX)
SET @IndexCreationPlaceholder_Start = 'IF NOT EXISTS (SELECT * FROM {2}.sys.indexes WHERE [name] = ''IX_{0}'' ) BEGIN CREATE NONCLUSTERED INDEX [IX_{0}] ON {1}'
SET @IndexCreationPlaceholder_End = ' WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] END;' + char(13) + char(10)
SELECT DBName, CASE WHEN NOT mid.equality_columns IS NULL AND NOT mid.Inequality_columns IS NULL THEN REPLACE(REPLACE(REPLACE(@IndexCreationPlaceholder_Start,'{0}', mid.ID),'{1}',mid.[statement]),'{2}',mid.DBName) + ' ( ' + COALESCE(mid.equality_columns,'') + ' ASC,' + COALESCE(mid.Inequality_columns,'') + ' ASC )' + COALESCE('INCLUDE ( ' + mid.Included_columns + ' ) ','') + @IndexCreationPlaceholder_End
WHEN mid.equality_columns IS NULL AND NOT mid.Inequality_columns IS NULL THEN REPLACE(REPLACE(REPLACE(@IndexCreationPlaceholder_Start,'{0}', mid.ID),'{1}',mid.[statement]),'{2}',mid.DBName) + ' ( ' + COALESCE(mid.Inequality_columns,'') + ' ASC ) ' + COALESCE('INCLUDE ( ' + mid.Included_columns + ' ) ','') + @IndexCreationPlaceholder_End WHEN NOT mid.equality_columns IS NULL AND mid.Inequality_columns IS NULL THEN REPLACE(REPLACE(REPLACE(@IndexCreationPlaceholder_Start,'{0}', mid.ID),'{1}',mid.[statement]),'{2}',mid.DBName) + ' ( ' + COALESCE(mid.equality_columns,'') + ' ASC ) ' + COALESCE('INCLUDE ( ' + mid.Included_columns + ' ) ','') + @IndexCreationPlaceholder_End ELSE NULL END AS Index_Creation_Statement, ' DROP INDEX [IX_' + mid.ID + '] ON ' + mid.[statement] + + char(13) + char(10) AS Index_Drop_Statement FROM [dbo].[vw_Index_MissingIndex] AS mid
GO
|