How to retrieve the definition behind statistics added to tables

Question

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.

Answer 1

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)), 
    name 
    from sys.stats;

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

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.

Answer 2

At http://sqlserverpedia.com/wiki/Updating_Statistics (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.
SELECT
      N'CREATE STATISTICS ' +
      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 
                              sc1.stats_id,
                              sc1.stats_column_id,
                                c1.name
                           FOR XML PATH( '' )
                          ), 3, 4000 ) 
      +
      N')'
FROM
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
GROUP BY  
    sc2.object_id
   , s.name
   , s.stats_id
   , s.auto_created
ORDER BY s.stats_id