Friday 17 February 2017

update stats modified date in SQL server




SELECT  sp.stats_id ,
        OBJECT_NAME(s.object_id) AS tablename ,
        s.name ,
sp.last_updated,
        sp.rows ,
        sp.rows_sampled ,
        CAST(sp.rows_sampled AS NUMERIC) / CAST(sp.rows AS NUMERIC) * 100 AS Percentage_sampled ,
        CASE WHEN sp.modification_counter = 0 THEN 0
             ELSE CAST(sp.modification_counter AS NUMERIC)
                  / CAST(sp.rows AS NUMERIC) * 100
        END AS Percent_changed ,
        CASE WHEN sp.modification_counter = 0 THEN 'No Modifications'
ELSE
CAST(sp.modification_counter AS VARCHAR(20))
END AS Modification_counter
FROM    sys.stats AS s
        CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id)
        AS sp
WHERE   sp.last_updated <= DATEADD(DAY, -1, GETDATE())
        AND sp.rows > 10000

No comments:

Post a Comment

Check if column exists or not in the SQL server

--method 1 IF EXISTS(SELECT 1 FROM sys.columns with (nolock)           WHERE Name = N'LoginName'           AND Object_ID = Objec...