As far as out of date statistics are concerned, it would be totally upto you which would you consider out of date. In following script I have marked the statistics out of date if the actual rows count are not equal to the rowscount for the stats. Now you can tweak the criteria according to your requirement.
IF OBJECT_ID('tempdb..#AllTables') IS NOT NULL
DROP TABLE #AllTables
IF OBJECT_ID('tempdb..#SHOWSTATISTICSOUTPUT') IS NOT NULL
DROP TABLE #SHOWSTATISTICSOUTPUT
DECLARE @SchemaName NVARCHAR(128)
, @TableName NVARCHAR(128)
, @Index_STATS_Name NVARCHAR(128)
CREATE TABLE #SHOWSTATISTICSOUTPUT
(
ID INT IDENTITY
PRIMARY KEY
,[NAME] NVARCHAR(128)
,LastUpdatedDate DATETIME
,[Rows] BIGINT
,[Rows Sampled] BIGINT
,[Steps] INT
,Density INT
,[Average key length] INT
,[String Index] VARCHAR(10)
)
SELECT DISTINCT ISNULL(SCHEMA_NAME([T].[schema_id]),'dbo') SchemaName
, [T].[name] TableName
, ISNULL(( SELECT SUM(spart.rows)
FROM sys.partitions spart
WHERE spart.object_id = T.object_id
AND spart.index_id < 2
), 0) AS [RowCount]
, ISNULL([i].[name], [s].[name]) AS Index_STATS_Name
, CAST(0 AS BIGINT) AS StatsRowCount
INTO #AllTables
FROM sys.[tables] AS T
INNER JOIN sys.[indexes] AS I
ON [T].[object_id] = [I].[object_id]
INNER JOIN sys.[stats] AS S
ON [I].[object_id] = [S].[object_id]
WHERE [i].[index_id] > 0
CREATE CLUSTERED INDEX IX_CLUS ON [#AllTables] ([TableName],[SchemaName], [Index_STATS_Name])
DECLARE PopulateStatsRows CURSOR FAST_FORWARD LOCAL FOR
SELECT [SchemaName]
, [TableName]
, [Index_STATS_Name]
FROM [#AllTables]
OPEN PopulateStatsRows
FETCH NEXT FROM PopulateStatsRows INTO @SchemaName
, @TableName
, @Index_STATS_Name
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT [#SHOWSTATISTICSOUTPUT]
(
[NAME]
,[LastUpdatedDate]
,[Rows]
,[Rows Sampled]
,[Steps]
,[Density]
,[Average key length]
,[String Index]
)
EXECUTE ('DBCC SHOW_STATISTICS('''+ @SchemaName + '.' + @TableName + ''',' + @Index_STATS_Name + ') WITH STAT_HEADER')
UPDATE AT
SET [StatsRowCount] = ISNULL([Rows],0)
FROM [#AllTables] AS AT
INNER JOIN [#SHOWSTATISTICSOUTPUT] AS S
ON [NAME] = [Index_STATS_Name]
WHERE [Index_STATS_Name] = @Index_STATS_Name
AND [TableName] = @TableName
AND [SchemaName] = @SchemaName
FETCH NEXT FROM PopulateStatsRows INTO @SchemaName
, @TableName
, @Index_STATS_Name
END
CLOSE PopulateStatsRows
DEALLOCATE PopulateStatsRows
SELECT *, CASE WHEN [RowCount] <> [StatsRowCount] THEN 'Yes' ELSE 'No' END AreStatsOutOfDate
FROM [#AllTables] AS AT
--SELECT * FROM [#SHOWSTATISTICSOUTPUT] AS S
DROP TABLE [#AllTables], [#SHOWSTATISTICSOUTPUT]
But for general purpose, I like Glenn Berry's scripts to find the statistics which are not updated
-- Glenn Berry
-- http://glennberrysqlperformance.spaces.live.com/
-- When were Statistics last updated on all indexes?
SELECT o.name, i.name AS [Index Name],
STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date],
s.auto_created, s.no_recompute, s.user_created
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON i.[object_id] = s.[object_id]
AND i.index_id = s.stats_id
WHERE o.[type] = 'U'
ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC;
-- Find indexes with no_recompute turned on
SELECT o.name, i.name AS [Index Name],
STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date],
s.auto_created, s.no_recompute, s.user_created
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON i.[object_id] = s.[object_id]
AND i.index_id = s.stats_id
WHERE o.[type] = 'U'
AND no_recompute = 1
ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC;
3 People are following this question.