SET QUOTED_IDENTIFIER ON
GO
-- Determines size of all auto-stats in a database:
SELECT CAST( SUM( DATALENGTH( i.statblob ) )  / (1024. * 1024.) AS varchar
       ) + ' MB' AS TotalAutoStatsSize
FROM sysindexes AS i
WHERE INDEXPROPERTY( i."id", i."name", 'IsAutoStatistics') = 1

-- Determines size of all indexes & statistics for each user table:
SELECT ( CASE WHEN GROUPING( i."id" ) = 1
                THEN '<>'
                ELSE OBJECT_NAME( i."id" )
       END ) AS "Table Name" ,  
       SUM( CASE WHEN ( INDEXPROPERTY( i."id" , i."name" , 'IsAutoStatistics' ) = 0 And
                        INDEXPROPERTY( i."id" , i."name" , 'IsHypothetical') = 0 )
                THEN DATALENGTH( i.statblob )
       END ) / (1024.) AS IndexSize,
       SUM( CASE WHEN INDEXPROPERTY( i."id" , i."name" , 'IsAutoStatistics') = 1
                THEN DATALENGTH( i.statblob )
       END ) / (1024.) AS StatsSize
  FROM sysindexes AS i
 WHERE OBJECTPROPERTY( i."id" , 'IsUserTable' ) = 1 And
       OBJECTPROPERTY( i."id" , 'IsMSShipped' ) = 0
 GROUP BY i."id"
  WITH ROLLUP
 ORDER BY "Table Name"
GO

This page was last updated on May 01, 2006 04:28 PM.