SE の雑記

DECLARE @sql nvarchar(max) = N’DBCC SHOW_STATISTICS (”@1”, ”@2”) WITH STAT_HEADER’
DECLARE @execsql nvarchar(max)

DECLARE @Table sysname, @Schema sysname, @Stats sysname

DECLARE StatsCursor CURSOR FORWARD_ONLY FOR
SELECT OBJECT_NAME(object_id),OBJECT_SCHEMA_NAME (object_id),name FROM sys.stats WHERE OBJECT_SCHEMA_NAME (object_id) <> ‘sys’
FOR READ ONLY

OPEN StatsCursor

FETCH NEXT FROM StatsCursor
INTO @Table, @Schema, @Stats

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @execsql = REPLACE(REPLACE(@sql, ‘@1’, @Schema + ‘.’ + @Table), ‘@2’, @Stats)
    EXEC (@execsql)
    FETCH NEXT FROM StatsCursor
    INTO @Table, @Schema, @Stats
END

DEALLOCATE StatsCursor

SELECT
    OBJECT_NAME(dsp.object_id)
    , dsp.object_id
    , ss.name
    , dsp.last_updated
    , dsp.rows
    , dsp.rows_sampled
    , dsp.steps
    , dsp.unfiltered_rows
    , dsp.modification_counter
FROM
    sys.stats AS ss
    CROSS APPLY
    sys.dm_db_stats_properties(ss.object_id, ss.stats_id) AS dsp
WHERE
    OBJECT_SCHEMA_NAME(dsp.object_id, DB_ID()) <> ‘sys’