Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there a way to programmatically retrieve the definition of each STATISTICS added to table columns and indexes. For both user added and system created indexes. There are many STATISTICS like '__WA_Sys_*' that are added by Sql Server.

I need to re-write some of them and add more, but there are too many to do them manually with Management Studio.

share|improve this question
Why do you need to rewrite the system-generated stats? –  JNK Mar 8 '12 at 21:36
To those that cast down votes, please consider if it was warranted. Your opinion may be that what the OP wants to do is not right but that's very different to the question being poor quality. –  Mark Storey-Smith Mar 8 '12 at 22:53
I guess my reasoning was not clear.Its not important that I re-write the system generated Stats, but more like I would like to add proper naming convention to each object (including stats) and add it to DB code version control. I understand that there will always be new system generated stats added, but this could be an on-going task.More importantly, some of the stats were added by previous DBA's but there is no code saved in Version Control.So I need to add code to version control about user generated stats. Thank you for the comments,I see the gap in explaining my question earlier. –  UB01 Mar 8 '12 at 23:20
@MarkStorey-Smith thank you for editing the question. –  UB01 Mar 12 '12 at 14:09
add comment

2 Answers

up vote 1 down vote accepted

At http://www.toadworld.com/platforms/sql-server/w/wiki/9680.updating-statistics.aspx (good read) there is a query that I don't fully understand, but I converted it into this, below (SQL Server 2005). Output:

CREATE STATISTICS [IX_student_2012_1] ON [student_2012] ([student_key])
CREATE STATISTICS [IX_student_2012_2] ON [student_2012] ([dbname], [id])
CREATE STATISTICS [IX_student_2012_3] ON [student_2012] ([forename], [surname], [dob])
CREATE STATISTICS [_WA_Sys_00000003_158238E1] ON [student_2012] ([surname])
CREATE STATISTICS [_WA_Sys_00000051_158238E1] ON [student_2012] ([Own_coun])
CREATE STATISTICS [_WA_Sys_00000010_158238E1] ON [student_2012] ([ethnic])

Obviously some of those come from indexes. You can probably improve the query to remove those; you don't need copies. And you can add "WITH FULLSCAN", of course.

I'm interested in this, because we load enterprise data into some databases overnight, so that reports can be run during the day. If I generate some statistics during the overnight run - "WITH FULLSCAN" - then the reports will run better because they don't need to make the statistics. If SQL Server creates statistics, I don't assume that there really needs to be an index on the columns instead, but I will think about it. In fact I don't understand[+] why ever have just statistics when you can have indexes, but I assume there are good reasons! [+]Well, I understand a bit - otherwise that would be not a very good article!

Robert Carnegie, likes indexes!

--Generate Statistics definitions for a given table.
      QUOTENAME(s.name) +
      N' ON ' + 
      QUOTENAME(OBJECT_NAME(sc2.object_id)) +
      N' (' +
      SUBSTRING((SELECT ( ', ' + QUOTENAME(c1.name) )
       FROM sys.stats_columns sc1 JOIN sys.columns c1
                            ON sc1.object_id = c1.object_id
                            AND sc1.column_id = c1.column_id

                          WHERE sc1.object_id = sc2.object_id 
                            AND sc1.stats_id = s.stats_id
                           ORDER BY 
                           FOR XML PATH( '' )
                          ), 3, 4000 ) 
sys.stats_columns sc2 JOIN sys.columns c2
    ON sc2.object_id = c2.object_id
    AND sc2.column_id = c2.column_id 
JOIN sys.stats s
    ON sc2.object_id = s.object_id
    AND sc2.stats_id = s.stats_id
WHERE sc2.object_id = object_id('Student_2012') --substitute Tablename
   , s.name
   , s.stats_id
   , s.auto_created
ORDER BY s.stats_id
share|improve this answer
Thank you, this helpful. –  UB01 Sep 16 '13 at 14:25
add comment

Between sys.stats and DBCC SHOW_STATISTICS you have all the info needed to see the statistics of your statistics. Eg.:

set nocount on;
declare @object nvarchar(256), @stat sysname;
declare crs cursor forward_only read_only static for
select quotename(object_schema_name(object_id)) + 
    '.' +quotename(object_name(object_id)), 
    from sys.stats;

open crs;
fetch next from crs into @object, @stat;
while 0 = @@FETCH_STATUS
    dbcc show_statistics(@object, @stat) with NO_INFOMSGS, STAT_HEADER
    fetch next from crs into @object, @stat;

close crs;
deallocate crs;

That being said, anybody who understands when the stats need to be updated already knows how to look them up, so I would venture a guess you should not be updating your stats manually.

share|improve this answer
Ha...Ha... LOL. I see your point. I am familiar with sys.stats, STATS_DATE() and SHOW_STATISTICS. I was looking for something more simple i.e. like OBJECT_DEFINITION(). But thanks for comment. I'll just have to write some code that builds the definition. –  UB01 Mar 8 '12 at 23:08
add comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.