statistics related information

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

statistics related information

Author

Topic

mahajanakhil1985
Yak Posting Veteran

71 Posts

Posted - 2010-01-29 : 11:04:35

How can I see the stats collection date of all tables in database?

I tried out sys.tables but didn't find the required column. I don't want to go to each column under statistics option in Management Studio ans see the date of stats collection. Rather I want a single SQL command or a system view that could accomplish it.
I want to see such information for Indexes also as sys.indexes doesn't contain it.

When I work with Oracle DB, then sys.dba_tables and sys.dba_indexes have such information. So, I am trying to establish analogy with sys.tables and sys.indexes in SQL Server.

Kristen
Test

22859 Posts

Posted - 2010-01-29 : 11:57:25

STATS_DATE() ??

agiotti
Starting Member

37 Posts

Posted - 2010-01-29 : 17:15:07

Here is a script I use.

DECLARE @i int
DECLARE @schName sysname
DECLARE @tableName sysname
DECLARE @index sysname
DECLARE @SQL nvarchar(300)
SET @i = 1

IF OBJECT_ID('TEMPDB..#ShowStats') IS NOT NULL DROP TABLE #ShowStats
CREATE TABLE #ShowStats
(
IdxName sysname
, Updated datetime
, Rows int
, RowsSampled int
, Steps int
, Density int
, AvgKeyLength int
, StringIdx char (3)
)

IF OBJECT_ID('TEMPDB..#indexes') IS NOT NULL DROP TABLE #indexes
CREATE TABLE #indexes
(
id int identity
, schemaName sysname
, tableName sysname
, indexName sysname
)

INSERT INTO #indexes (schemaName, tableName, indexName)
SELECT schema_name(so.schema_id),object_name(si.object_id), si.name
FROM sys.indexes si INNER JOIN sys.objects so ON si.object_id = so.object_id
WHERE si.object_id >100
AND so.type = 'U'
AND si.index_id > 0

WHILE
@i < (SELECT max(id) FROM #indexes)

BEGIN
SELECT @schName = schemaName
, @tableName = tableName
, @index = indexName
FROM #indexes
WHERE id = @i

SET @SQL = N'DBCC SHOW_STATISTICS (['+ @schName + N'.' + @tableName + N'],' + @index + N')' + N'WITH STAT_HEADER'
INSERT INTO #ShowStats EXEC sp_executesql @SQL

SET @i = @i + 1
END

SELECT * FROM #ShowStats order by 3 desc

DROP TABLE #indexes
DROP TABLE #ShowStats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources