DROP PROCEDURE dbo.usp_GetTableSizes_2000
Go

CREATE PROCEDURE dbo.usp_GetTableSizes_2000
AS

/**********************************************************
Purpose:   To retrieve table and index sizes for all tables
           in a DB.  Index and Statistics (ie '_wa_%') counts and
           index statistics update date are also included
          
Author:    ChillyDBA
History:   2001 - Initial Release
***********************************************************/

CREATE TABLE #TableSizes
(
   name                SYSNAME,
   rows                INT,
   reserved            VARCHAR(30),
   data                VARCHAR(30),
   index_size          VARCHAR(30),
   unused              VARCHAR(30)
)


CREATE TABLE #TableIndexes
(
   name                SYSNAME,
   total_indexes       INT,
   total_clustered     INT,
   total_nonclustered  INT,
   total_statistics    INT,
   stats_last_updated  DATETIME
)


DECLARE
   @SnapshotDate       DATETIME,   -- in case this information is to be persisted for trend analysis
   @TableName          SYSNAME,
   @Cmd                VARCHAR(1000)

SELECT @SnapshotDate = GETDATE()

SELECT @TableName = MIN(name)
FROM sysobjects
WHERE TYPE = 'u'
AND name NOT LIKE 'sys%'


WHILE @TableName IS NOT NULL
BEGIN
   SELECT @Cmd = 'sp_spaceused ' + @TableName

   INSERT #TableSizes
   EXEC (@Cmd)

   INSERT #TableIndexes (name, total_indexes, total_nonclustered, total_clustered, total_statistics, stats_last_updated)
   SELECT @TableName,
       -- get # indexes that are not system stats
       (SELECT COUNT(*)
       FROM sysindexes
       WHERE id = OBJECT_ID(@TableName)
       AND name NOT LIKE '_wa_%'
       AND indid > 0),

       -- get # nonclustered indexes that are not system stats
       (SELECT COUNT(*)
       FROM sysindexes
       WHERE id = OBJECT_ID(@TableName)
       AND name NOT LIKE '_wa_%'
       AND indid > 1),

       -- get # clustered indexes that are not system stats
      (SELECT COUNT(*)
       FROM sysindexes
       WHERE id = OBJECT_ID(@TableName)
       AND name NOT LIKE '_wa_%'
       AND indid = 1),

       -- get # system stats
       (SELECT COUNT(*)
       FROM sysindexes
       WHERE id = OBJECT_ID(@TableName)
       AND name LIKE '_wa_%'
       AND indid > 0),

       -- get date that index stats were last updated
       (SELECT MAX(STATS_DATE(ID, indid))
       FROM sysindexes
       WHERE id = OBJECT_ID(@TableName))

   SELECT @TableName = MIN(name)
   FROM sysobjects
   WHERE TYPE = 'u'
   AND name > @TableName
   AND name NOT LIKE 'sys%'
   AND uid = 1
END

--insert dba_table_sizes
SELECT
   DB_NAME()                                       AS DatabaseName,
   ts.name                                         AS TableName,
  
   -- remove the kb suffix so that these figures can be manipulated easily in excel or any other query
   REPLACE(rows, 'kb','')                          AS #Rows,
   REPLACE(reserved, 'kb','')                      AS KB_Reserved,
   REPLACE(data, 'kb','')                          AS KB_Data,
   REPLACE(index_size, 'kb','')                    AS KB_Index,
   REPLACE(unused, 'kb','')                        AS KB_Unused,
  
   ti.total_indexes                                AS #TotalIndexes,
   ti.total_nonclustered                           AS #NonClusteredIndexes,
   ti.total_clustered                              AS #ClusteredIndexes,
   ti.total_statistics                             AS #Statistics,
   ti.stats_last_updated                           AS StatsLastUpdatedDate,
   DATEDIFF( dd, ti.stats_last_updated, GETDATE()) AS StatsAgeInDays,
   @SnapshotDate                                   AS SnapshotDate
FROM #TableSizes ts
INNER JOIN #TableIndexes ti
   ON ts.name = ti.name
--order by cast(replace(reserved, 'kb','')as int) desc
ORDER BY ts.name


DROP TABLE #TableSizes
DROP TABLE #TableIndexes


DROP PROCEDURE dbo.usp_GetTableSizes_2005
Go

CREATE PROCEDURE dbo.usp_GetTableSizes_2005
AS

/**********************************************************
Purpose:   To retrieve table and index sizes for all tables
           in a DB.  Index and Statistics (ie '_wa_%') counts and
           index statistics update date are also included
          
Author:    Jeffrey Yao
History:   27 Sep 2010 - Initial Release
          19 Jun 2012 - ChillyDBA - Augmented with # coumns to match the
                   output with the usp_GetTableSizes_2000 SP  
***********************************************************/
DECLARE
       @SnapshotDate       DATETIME    -- in case this information is to be persisted for trend analysis

SELECT @SnapshotDate = GETDATE()


SELECT
   DB_Name()                                                           AS DatabaseName,
   object_schema_name(OBJECT_ID) + '.' + OBJECT_NAME(OBJECT_ID)        AS TableName,
   SUM(CASE WHEN index_id < 2 THEN row_count ELSE 0 END)                AS #Rows,
   8*SUM(reserved_page_count)                                          AS KB_Reserved,
  
   8*SUM
       (   CASE
               WHEN index_id<2 THEN in_row_data_page_count
                                       + lob_used_page_count
                                       + row_overflow_used_page_count
               ELSE lob_used_page_count
                       + row_overflow_used_page_count
           END
       )                                                               AS KB_Data,
      
   8*
       (
           SUM(used_page_count) - SUM
                                   (   CASE
                                       WHEN index_id<2 THEN in_row_data_page_count
                                                               + lob_used_page_count
                                                               + row_overflow_used_page_count
                                       ELSE lob_used_page_count
                                               + row_overflow_used_page_count
                                       END
                                   )
       )                                                               AS KB_Index,
          
   8*SUM(reserved_page_count-used_page_count)                          AS KB_Unused,
  

   (
       SELECT COUNT(*)
       FROM sys.dm_db_partition_stats ps2 (NOLOCK)
       WHERE ps.object_id = ps2.object_id
       AND OBJECT_NAME(ps2.object_ID) NOT LIKE '_wa_%'
       AND ps2.index_id > 0
   )                                                                   AS #TotalIndexes,

   (
       SELECT COUNT(*)
       FROM sys.dm_db_partition_stats ps3 (NOLOCK)
       WHERE ps.object_id = ps3.object_id
       AND OBJECT_NAME(ps3.object_ID) NOT LIKE '_wa_%'
       AND ps3.index_id > 1
   )                                                                   AS #TotalNonClusteredIndexes,

   (
       SELECT COUNT(*)
       FROM sys.dm_db_partition_stats ps4 (NOLOCK)
       WHERE ps.object_id = ps4.object_id
       AND OBJECT_NAME(ps4.object_ID) NOT LIKE '_wa_%'
       AND ps4.index_id = 1
   )                                                                   AS #TotalClusteredIndexes,

   (
       SELECT COUNT(*)
       FROM sysindexes si (NOLOCK)
       WHERE ps.object_id = si.id
       AND si.name LIKE '_wa_%'
       AND si.indid > 0
   )                                                                   AS #Statistics,
  
   (
       SELECT MAX(STATS_DATE(object_id, index_id))
       FROM sys.dm_db_partition_stats ps6 (NOLOCK)
       WHERE ps.object_id = ps6.object_id
   )                                                                   AS StatsLastUpdatedDate,
  
   DATEDIFF( dd,    (
                       SELECT MAX(STATS_DATE(object_id, index_id))
                       FROM sys.dm_db_partition_stats ps6 (NOLOCK)
                       WHERE ps.object_id = ps6.object_id
                   ), GETDATE())                                        AS  StatsAgeInDays,
  
   @SnapshotDate                                                       AS SnapshotDate
FROM sys.dm_db_partition_stats ps  (NOLOCK)
WHERE OBJECT_ID > 1024
GROUP BY OBJECT_ID
ORDER BY TableName