-
BEGIN TRY
DECLARE @tableName VARCHAR(255)=NULL --'dbo.MaTable'
,@stat_num INT
,@stat_number INT
,@StatName SYSNAME
,@stats_id INT
,@is_incrementall BIT
,@ObjectID INT
,@requete NVARCHAR(max)
,@error_num INT
,@error_message VARCHAR(4000)
,@num_lig INT
,@nbre_lig INT
DROP TABLE IF EXISTS #Table_List
DROP TABLE IF EXISTS #Statistics_List
SELECT IDENTITY(INT,1,1) num_lig, tableName INTO #Table_List FROM
(
SELECT tableName FROM (SELECT @tableName AS tableName)T1
UNION
SELECT SCHEMA_NAME(schema_id) + '.' + [name] AS tableName FROM sys.tables WHERE [type]='U'
) R1
SELECT @num_lig=1, @nbre_lig= CASE WHEN @tableName IS NULL THEN COUNT(*) ELSE 1 END FROM #Table_List
WHILE @num_lig <= @nbre_lig
BEGIN
DROP TABLE IF EXISTS #Statistics_List
SELECT @ObjectID = OBJECT_ID(TableName) FROM #Table_List WHERE num_lig=@num_lig
SELECT IDENTITY(INT,1,1) num_stat,
[name],
stats_id,
is_incremental
INTO #Statistics_List
FROM sys.stats
WHERE auto_created=0
AND object_id = @ObjectID
SELECT @stat_num=1, @stat_number=COUNT(*) FROM #Statistics_List
WHILE @stat_num <= @stat_number
BEGIN
SELECT @StatName = [name], @stats_id = stats_id, @is_incrementall=is_incremental
FROM #Statistics_List WHERE num_stat=@stat_num
IF @is_incrementall = 1
BEGIN
-- Vérifier si la partition a a été modifiée
IF EXISTS(SELECT 1 FROM sys.dm_db_incremental_stats_properties(@ObjectID, @stats_id) WHERE modification_counter > 0 )
BEGIN
PRINT 'MAJ Statistiquese incrementale : ' + @StatName;
SELECT @requete = 'UPDATE STATISTICS ' + @TableName + ' ( ' + QUOTENAME( @StatName) + ' ) WITH RESAMPLE, INCREMENTAL = ON'
EXEC(@requete);
END
END
ELSE
BEGIN
-- Verifier si la statistique non incrementale est obsolete ou pas
IF EXISTS(SELECT 1 FROM sys.dm_db_stats_properties(@ObjectID, @stats_id) WHERE modification_counter > 0 )
BEGIN
PRINT 'MAJ statistique NON incrementale : ' + @StatName;
SELECT @requete = 'UPDATE STATISTICS ' + @TableName + ' ( ' + QUOTENAME( @StatName ) + ' ) WITH RESAMPLE'
EXEC(@requete);
END
END
SELECT @stat_num = @stat_num + 1
END
SELECT @num_lig = @num_lig + 1
END
END TRY
BEGIN CATCH
SELECT @error_num=@@ERROR, @error_message=ERROR_MESSAGE()
END CATCH
DROP TABLE IF EXISTS #Table_List
DROP TABLE IF EXISTS #Statistics_List