SqlServer自动创建缺少的索引

脚本如下

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 fn_Index_CreateIndexName
--drop FUNCTION [dbo].[fn_Index_CreateIndexName];
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 FUNCTION vw_Index_MissingIndex
--drop VIEW [dbo].[vw_Index_MissingIndex]
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
--Drop PROCEDURE [dbo].[usp_Index_MissingIndexCreationStatements];
CREATE PROCEDURE [dbo].[usp_Index_MissingIndexCreationStatements]
AS

DECLARE @IndexCreationPlaceholder_Start AS NVARCHAR(MAX)
DECLARE @IndexCreationPlaceholder_End AS NVARCHAR(MAX)

-- PREPARE PLACEHOLDER

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)

-- STATEMENT CREATION

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