[SQL Server] Mise à jour des statistiques sql
BEGIN TRY
DECLARE @tableName VARCHAR(255)='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)
DROP TABLE IF EXISTS #Statistics_List
SELECT @tableName = CASE WHEN SUBSTRING(@TableName,1,4)<>'dbo.' THEN 'dbo.' + @TableName ELSE @TableName END
SELECT @ObjectID = OBJECT_ID(@TableName)
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
END TRY
BEGIN CATCH
SELECT @error_num=@@ERROR, @error_message=ERROR_MESSAGE()
END CATCH
DROP TABLE IF EXISTS #Statistics_List