Technical Article

Update Statistics - All Tables (No System)

,

This proc executing the update statistics command in all user tables (no system).

/* 
** TIPO DO OBJETO : Procedure
** NOME DO OBJETO : usp_updt_stat
** AUTOR : Benes Guislandi
** DATA : 24/10/2007 
** SISTEMA : Administring MSSQL
** OBJETIVO : Show Datafiles of databases
** MANUTEÇÃO : 
** DATA DA MANUTENÇÃO : 
** OBS DA MANUTENÇÃO : 
*/CREATE PROCEDURE usp_updt_stat 
AS
--
declare @name varchar(60), 
@command varchar(255), 
@msg varchar(255),
@name_u varchar(30)
--
SELECT @name = min(table_name) 
FROM information_schema.tables 
WHERE table_name NOT LIKE '%BKP%' 
and table_type = 'base table' 
and table_schema = 'dbo'
--
while @name is not null 
begin
select @command = 'PRINT ''EXECUTING UPDT_STATS IN: '+UPPER(@name)+''''+CHAR(13)+
'update statistics ' +@name+ CHAR(13)+
'PRINT ''================================================='''+CHAR(13)
exec(@command)
if @@error <> 0
begin
select @msg = 'Error in Update Statistics: ' + @command + '.'
raiserror(@msg,16,1) with log
end
--
SELECT @name = min(table_name) 
FROM information_schema.tables 
WHERE table_name NOT LIKE '%BKP%' 
and table_type = 'base table' 
and table_name > @name 
and table_schema = 'dbo'
end
-- ================================================================================== 
-- TO CONFIRM: THE FOLLOWING PROC SHOW THE LAST UPDATE STATISTICS OF TBALES --
-- sp_autostats <TABLE_NAME> --

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating