Script to automate the Stats history
Following on my from posting on Cardinality earlier this month, As I will likely want a way to quickly evaluate the stats history for a database
http://sqlblogcasts.com/blogs/neilhambly/archive/2012/01/14/show-me-some-cardinality.aspx
I can of course automate the processs with something like the following script - which can be run against a database to provide a summary of the statistics for a given database
Have a Happy Stats day
SET NOCOUNT ON
CREATE TABLE #STATS
(
TABLE_CATALOG VARCHAR(128)
,TABLE_SCHEMA VARCHAR(128)
,TABLE_NAME VARCHAR(128)
,STATISTICS_CATALOG VARCHAR(128)
,STATISTICS_SCHEMA VARCHAR(128)
,STATISTICS_NAME VARCHAR(128)
,STATISTICS_TYPE INT
,COLUMN_NAME VARCHAR(128)
,COLUMN_GUID VARCHAR(128)
,COLUMN_PROPID VARCHAR(128)
,ORDINAL_POSITION INT
,SAMPLE_PCT INT
,LAST_UPDATE_TIME DATETIME
,NO_OF_RANGES INT
,COLUMN_CARDINALITY INT
,TUPLE_CARDINALITY INT
,TABLE_CARDINALITY INT
,AVG_COLUMN_LENGTH INT
)
GO
DECLARE @DBName VARCHAR(64), @SchemaName VARCHAR(64), @TableName VARCHAR(64), @IndexName VARCHAR(64)
DECLARE curStats CURSOR FAST_FORWARD READ_ONLY FOR
SELECT
QUOTENAME(DB_NAME()) AS DBName,
QUOTENAME(Schema_name(SO.schema_id)) AS SchemaName,
QUOTENAME(OBJECT_NAME(SO.OBJECT_ID)) AS TableName,
SI.name AS IndexName
FROM sys.indexes SI
INNER JOIN
sys.objects SO ON SI.object_id = SO.object_id
WHERE SI.name IS NOT NULL
AND Schema_name(SO.schema_id) NOT LIKE 'sys'
AND SO.type = 'U'
OPEN curStats
FETCH NEXT FROM curStats INTO @DBName,
@SchemaName, @TableName,
@IndexName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @SQLCmd NVARCHAR(2000)
SELECT @SQLCmd =
'INSERT #STATS EXEC (' + CHAR(39) + 'DBCC SHOW_STATISTICS ('
+ CHAR(39) + CHAR(39) + @DBname + '.' + @SchemaName + '.' + @TableName
+ CHAR(39) + CHAR(39) + ',' + CHAR(39) + CHAR(39) + @IndexName + CHAR(39)
+ CHAR(39) + ') WITH STAT_HEADER JOIN
DENSITY_VECTOR' + CHAR(39) + ');'
--SELECT @SQLCmd
EXEC (@SQLCmd)
FETCH NEXT FROM curStats INTO @DBName,
@SchemaName, @TableName,
@IndexName
END
CLOSE curStats
DEALLOCATE curStats
GO
SELECT *
FROM #STATS
WHERE SAMPLE_PCT <> 100 OR
LAST_UPDATE_TIME < GETDATE()-1
ORDER BY TABLE_SCHEMA ASC, TABLE_NAME ASC, STATISTICS_NAME ASC, ORDINAL_POSITION ASC
SET NOCOUNT OFF