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
|
declare @cmd varchar(1000)
declare @nomBase varchar(255)
declare @mStatus int
declare tmpO cursor for select name from master..sysdatabases
where name not in ('master', 'model', 'msdb', 'tempdb', 'redirection','ReportServerTempDB', 'ReportServer')
open tmpO
while 1 = 1
begin
fetch next from tmpO into @nomBase
if @@fetch_status <> 0 break
begin
select @mStatus = status from master..sysdatabases
where name = @nomBase
print @nomBase + ' '+ cast(@mStatus as varchar(10))
print '------------------------------ '+ @nomBase + ' - start ---------------------------'
select @cmd = 'exec '+ @nomBase +'.dbo.admin_UpdateStatistics '
exec(@cmd)
print '------------------------------ '+ @nomBase + ' - ok ---------------------------'
print ' '
end
end
close tmpO
deallocate tmpO
create procedure [dbo].[admin_UpdateStatistics]
as
begin transaction ReCreationIndexes
-- regÚnÚration des indexes
declare @tableName varchar(500)
declare @cmd varchar(300)
declare tmpC cursor for select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'
open tmpC
while 1 = 1
begin
fetch next from tmpC into @tableName
if @@fetch_status <> 0 break
begin
set @cmd = 'DBCC DBREINDEX ('''+ @tableName + ''')'
execute (@cmd)
end
end
close tmpC
deallocate tmpC
commit transaction ReCreationIndexes
-- update statistics
declare tmpD cursor for select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'
open tmpD
while 1 = 1
begin
fetch next from tmpD into @tableName
if @@fetch_status <> 0 break
begin
set @cmd = 'update statistics ' + @tableName
execute (@cmd)
end
end
close tmpD
deallocate tmpD
-- recompilation des proc. stockÚs.
declare tmpE cursor for select TABLE_NAME from INFORMATION_SCHEMA.TABLES
open tmpE
while 1 = 1
begin
fetch next from tmpE into @tableName
if @@fetch_status <> 0 break
begin
set @cmd = 'sp_recompile ' + @tableName
execute (@cmd)
end
end
close tmpE
deallocate tmpE |
Maintenance des indexes et statistiques
Répondre avec citation
Envoyé par SQLpro
Partager