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

Share

Share

Rate