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
| DECLARE @NAME_BASE VARCHAR(255)
DECLARE @NAME_TABLE VARCHAR(255)
DECLARE @NAME_INDEX VARCHAR(255)
DECLARE @SQL1 NVARCHAR(1000)
DECLARE @SQL2 NVARCHAR(1000)
DECLARE @SQL3 NVARCHAR(1000)
DECLARE @ID_BDD INT
DECLARE @ID_OBJECT INT
DECLARE @ID_INDEX INT
DECLARE @FRAG FLOAT
DECLARE @NOM_TABLE VARCHAR(500)
CREATE TABLE TEMPTABLE (
ID INT,
NAME VARCHAR(500),
INDEXID INT,
FRAG FLOAT,
NOM_TABLE VARCHAR(500)
)
DECLARE @PARCOURS_BASE AS CURSOR
SET @PARCOURS_BASE=CURSOR FOR SELECT name, database_id FROM sys.databases where NAME NOT IN ('master','model','msdb','tempdb')
OPEN @PARCOURS_BASE
/* on se place au premier enregistrement*/
FETCH @PARCOURS_BASE INTO @NAME_BASE, @ID_BDD
/*on parcourt les bases*/
WHILE (@@FETCH_STATUS=0)
BEGIN
PRINT 'base:'
PRINT @NAME_BASE
SET @SQL1 = 'INSERT INTO TEMPTABLE (ID,NAME,INDEXID) SELECT object_id,name,index_id FROM ' + @NAME_BASE + '.sys.indexes WHERE name not like ''PK_%'' AND name not like ''clust'' AND name not like ''clst'' AND name not like ''nc%'' AND name not like ''queue_secondary_index'' AND name not like ''docid'' AND name not like ''queue_clustered_index'' AND name not like ''cl'' AND name not like''i%'' '
PRINT @SQL1
EXECUTE(@SQL1)
DECLARE @PARCOURS_TABLE AS CURSOR
SET @PARCOURS_TABLE=CURSOR FOR SELECT ID,NAME,INDEXID FROM TEMPTABLE FOR UPDATE
OPEN @PARCOURS_TABLE
FETCH @PARCOURS_TABLE INTO @ID_OBJECT, @NAME_INDEX, @ID_INDEX
WHILE (@@FETCH_STATUS=0)
BEGIN
PRINT @ID_BDD
PRINT @ID_OBJECT
PRINT @ID_INDEX
SET @SQL2 = 'UPDATE TEMPTABLE SET FRAG=(SELECT AVG(avg_fragmentation_in_percent) FROM ' + @NAME_BASE + '.sys.dm_db_index_physical_stats(' + CAST(@ID_BDD AS VARCHAR(10)) + ',' + CAST(@ID_OBJECT AS VARCHAR(10)) + ',' + CAST(@ID_INDEX AS VARCHAR(10)) + ',NULL,NULL)) WHERE ID='+ CAST(@ID_OBJECT AS VARCHAR(10)) + ' AND NAME=''' + @NAME_INDEX + ''' AND INDEXID=' + CAST(@ID_INDEX AS VARCHAR(10))
PRINT @SQL2
EXEC(@SQl2)
SELECT
@FRAG=FRAG
FROM
TEMPTABLE
WHERE
ID=@ID_OBJECT AND
NAME = @NAME_INDEX AND
INDEXID = @ID_INDEX
print @NAME_INDEX
print ('frag:')
print @FRAG
SET @SQL3 = 'UPDATE TEMPTABLE SET NOM_TABLE=(SELECT name FROM ' + @NAME_BASE + '.sys.tables WHERE ID='+ CAST(@ID_OBJECT AS VARCHAR(10)) + ')'
PRINT @SQL3
EXEC(@SQL3)
/*récupération nom de la table pour reconstruction DBCC DBREINDEX
SET @SQL3 = 'SELECT NAME_TABLE =name FROM ' + @NAME_BASE + '.sys.tables WHERE object_id=@ID_OBJECT ' PRINT @SQL3
EXEC(@SQL3)
*/
SELECT
@NOM_TABLE=NOM_TABLE
FROM
TEMPTABLE
WHERE
ID=@ID_OBJECT AND
NAME = @NAME_INDEX AND
INDEXID = @ID_INDEX
PRINT ('nom de la table:')
PRINT @NOM_TABLE
/*IF @FRAG >=30.0
BEGIN
Set @Requete = 'DBCC DBREINDEX ('+@NAME_TABLE+', '+@NAME_INDEX+',80)'
Exec (@Requete)
print ('reconstruction')
END*/
FETCH @PARCOURS_TABLE INTO @ID_OBJECT, @NAME_INDEX, @ID_INDEX
END
CLOSE @PARCOURS_TABLE
DEALLOCATE @PARCOURS_TABLE
/*on change de ligne de base*/
DELETE FROM TEMPTABLE
FETCH @PARCOURS_BASE INTO @NAME_BASE, @ID_BDD
END
CLOSE @PARCOURS_BASE
DEALLOCATE @PARCOURS_BASE
DROP TABLE TEMPTABLE |
