Find Statistics Info for all tables | SansSQL

Sunday, September 6, 2009

Find Statistics Info for all tables

Here is the code to find Statistics info (if exists) of all the tables in a database.
This code is tested for SQL 2005 and SQL 2008.

If exists (Select * from sys.tables where name='StatsInfo')
Drop Table StatsInfo

Create Table StatsInfo
(TableName varchar(500),
ColumnName varchar(500),
StatsName varchar(500))

Exec sp_msforeachtable 'insert into StatsInfo (StatsName,ColumnName)
Exec sp_helpstats ''?'';
Update StatsInfo set TableName=''?'' where TableName is NULL'

Select * from StatsInfo

2 comments:

Anonymous said...

Rather than using the unsupported sys.Tables, the supported INFORMATION_SCHEMA.tables and INFORMATION_SCHEMA.columns is a better way to go and its a lot easier and doesn't require a temporary table...

SELECT * FROM INFORMATION_SCHEMA.Tables

SELECT * FROM INFORMATION_SCHEMA.Columns

-Eric Isaacs

Sandesh Segu said...

Hi Eric,

Here the sys.tables is used to just check if the table StatsInfo is already present in the database or not and then drop the table if already exists.
sys.tables is unsupported in SQL 2000 where as it supported in all the next versions 2005 and 2008.
Moreover INFORMATION_SCHEMA.Tables are still included in 2005 and 2008to support the backward compatibality and dont know when this will be taken out.

Regards,
Sandesh Segu

Post a Comment

Ads