SQLServer_Scripts/SP_DBA_INDEXMONITOR.sql at master · nextaxtion/SQLServer_Scripts · GitHub

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
109
110
111
112
113
114
115
116
117
118
119
120
121
USE DBA_MONITOR
GO
DROP PROCEDURE IF EXISTS SP_DBA_INDEXMONITOR
GO
CREATE PROCEDURE SP_DBA_INDEXMONITOR
@DATABASES VARCHAR(1000) = 'USER_DATABASES' ,
@EXCEPT VARCHAR(1000) = '',
@DATABASE_DESTINATION VARCHAR(200) = 'DBA_MONITOR',
@TABLE VARCHAR(200) = 'RESULTADO_SHOWINDEX',
@PURGE_HISTORY_DAYS SMALLINT= 180
AS
DECLARE @COMMAND VARCHAR(1000) = ''
IF(@DATABASES = 'USER_DATABASES')
BEGIN
DECLARE @DBS TABLE(DBNAME VARCHAR(200))
DECLARE @DB_CURRENT1 VARCHAR(200)
;WITH Databases1 (StartPosition, EndPosition, DatabaseItem) AS
  (
  SELECT 1 AS StartPosition,
         ISNULL(NULLIF(CHARINDEX(',', @EXCEPT, 1), 0), LEN(@EXCEPT) + 1) AS EndPosition,
         SUBSTRING(@EXCEPT, 1, ISNULL(NULLIF(CHARINDEX(',', @EXCEPT, 1), 0), LEN(@EXCEPT) + 1) - 1) AS DatabaseItem
  WHERE @EXCEPT IS NOT NULL
  UNION ALL
  SELECT CAST(EndPosition AS int) + 1 AS StartPosition,
         ISNULL(NULLIF(CHARINDEX(',', @EXCEPT, EndPosition + 1), 0), LEN(@EXCEPT) + 1) AS EndPosition,
         SUBSTRING(@EXCEPT, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @EXCEPT, EndPosition + 1), 0), LEN(@EXCEPT) + 1) - EndPosition - 1) AS DatabaseItem
  FROM Databases1
  WHERE EndPosition < LEN(@EXCEPT) + 1
  )

INSERT INTO @DBS
SELECT NAME FROM SYS.DATABASES WHERE database_id > 4 AND state_desc = 'ONLINE'
AND NOT EXISTS(SELECT 1 FROM Databases1 WHERE NAME = DATABASEITEM)

DECLARE DATABASES_CURSOR CURSOR FOR
SELECT * FROM @DBS

OPEN DATABASES_CURSOR
FETCH NEXT FROM DATABASES_CURSOR INTO @DB_CURRENT1
WHILE @@FETCH_STATUS = 0
BEGIN


	SET @COMMAND = 'USE ' + @DB_CURRENT1 + CHAR(10) +
	' EXEC SP_SHOWINDEX @INDEX_DETAILS = 1,
	 @INDEX_FRAGMENTATION = 1,
	 @OUTPUT_COLUMNS =''SERVER_NAME,DATABASE_NAME,TABLE_NAME,INDEX_NAME,INDEX_TYPE,SEEKS,SCANS,LOOKUPS,UPDATES,PAGE_COUNT,ROW_COUNT,FILL_FACTOR,DATA_COMPRESSION,AVG_FRAGMENTATION_IN_PERCENT,COLUMNS,INCLUDE_COLUMNS,PRIMARY_KEY,LAST_SEEK,LAST_SCAN,LAST_LOOKUP,LAST_UPDATE,IS_DISABLED'',
	 @DESTINATION_TABLE = '''+@TABLE+''',
	 @DESTINATION_DATABASE = '''+@DATABASE_DESTINATION+'''
	'
	EXEC (@COMMAND)
FETCH NEXT FROM DATABASES_CURSOR INTO @DB_CURRENT1
END
CLOSE DATABASES_CURSOR
DEALLOCATE DATABASES_CURSOR
END
ELSE IF(@DATABASES <> 'USER_DATABASES')
BEGIN
DECLARE @DBS2 TABLE(DBNAME VARCHAR(200))
;WITH Databases1 (StartPosition, EndPosition, DatabaseItem) AS
  (
  SELECT 1 AS StartPosition,
         ISNULL(NULLIF(CHARINDEX(',', @Databases, 1), 0), LEN(@Databases) + 1) AS EndPosition,
         SUBSTRING(@Databases, 1, ISNULL(NULLIF(CHARINDEX(',', @Databases, 1), 0), LEN(@Databases) + 1) - 1) AS DatabaseItem
  WHERE @Databases IS NOT NULL
  UNION ALL
  SELECT CAST(EndPosition AS int) + 1 AS StartPosition,
         ISNULL(NULLIF(CHARINDEX(',', @Databases, EndPosition + 1), 0), LEN(@Databases) + 1) AS EndPosition,
         SUBSTRING(@Databases, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @Databases, EndPosition + 1), 0), LEN(@Databases) + 1) - EndPosition - 1) AS DatabaseItem
  FROM Databases1
  WHERE EndPosition < LEN(@Databases) + 1
  )
, Databases2 (StartPosition, EndPosition, DatabaseItem) AS
  (
  SELECT 1 AS StartPosition,
         ISNULL(NULLIF(CHARINDEX(',', @EXCEPT, 1), 0), LEN(@EXCEPT) + 1) AS EndPosition,
         SUBSTRING(@EXCEPT, 1, ISNULL(NULLIF(CHARINDEX(',', @EXCEPT, 1), 0), LEN(@EXCEPT) + 1) - 1) AS DatabaseItem
  WHERE @EXCEPT IS NOT NULL
  UNION ALL
  SELECT CAST(EndPosition AS int) + 1 AS StartPosition,
         ISNULL(NULLIF(CHARINDEX(',', @EXCEPT, EndPosition + 1), 0), LEN(@EXCEPT) + 1) AS EndPosition,
         SUBSTRING(@EXCEPT, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @EXCEPT, EndPosition + 1), 0), LEN(@EXCEPT) + 1) - EndPosition - 1) AS DatabaseItem
  FROM Databases2
  WHERE EndPosition < LEN(@EXCEPT) + 1
  )

INSERT INTO @DBS2
SELECT DatabaseItem FROM Databases1 A INNER JOIN SYS.DATABASES B ON A.DatabaseItem = B.NAME
WHERE B.state_desc = 'ONLINE' AND NOT EXISTS(SELECT 1 FROM Databases2 C WHERE A.DatabaseItem = C.DatabaseItem)

DECLARE @DB_CURRENT VARCHAR(200)
DECLARE DATABASES_CURSOR CURSOR FOR
SELECT * FROM @DBS2

OPEN DATABASES_CURSOR
FETCH NEXT FROM DATABASES_CURSOR INTO @DB_CURRENT
WHILE @@FETCH_STATUS = 0
BEGIN

	SET @COMMAND = 'USE ' + @DB_CURRENT + CHAR(10) +
	' EXEC SP_SHOWINDEX @INDEX_DETAILS = 1,
	 @INDEX_FRAGMENTATION = 1,
	 @OUTPUT_COLUMNS =''SERVER_NAME,DATABASE_NAME,TABLE_NAME,INDEX_NAME,INDEX_TYPE,SEEKS,SCANS,LOOKUPS,UPDATES,PAGE_COUNT,ROW_COUNT,FILL_FACTOR,DATA_COMPRESSION,AVG_FRAGMENTATION_IN_PERCENT,COLUMNS,INCLUDE_COLUMNS,PRIMARY_KEY,LAST_SEEK,LAST_SCAN,LAST_LOOKUP,LAST_UPDATE,IS_DISABLED'',
	 @DESTINATION_TABLE = '''+@TABLE+''',
	 @DESTINATION_DATABASE = '''+@DATABASE_DESTINATION+'''
	'
	EXEC (@COMMAND)
FETCH NEXT FROM DATABASES_CURSOR INTO @DB_CURRENT
END
CLOSE DATABASES_CURSOR
DEALLOCATE DATABASES_CURSOR

END

IF(@PURGE_HISTORY_DAYS > 0)
BEGIN
	DECLARE @CMD VARCHAR(1000) = ''
	SET @CMD = 'DELETE FROM ['+@DATABASE_DESTINATION+'].dbo.['+@TABLE+'] WHERE DATE_COLLECTION < DATEADD(DAY,-'+CAST(@PURGE_HISTORY_DAYS AS VARCHAR(10))+',GETDATE())'
	EXEC(@CMD)
END