0%

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