The joy of sp_spaceused

The joy of sp_spaceused

Copy and paste the code below into AdventureWorks2008 and execute against the DB you want table metrics for. The limitation of this script is that it can only give table metrics for the current DB as sp_spaceused only works for the current DB. Perhaps the next target will be to modify this so as to get the tables for all databases within a server.

/*

script to get table sizes and row count within a database using sp_spaceused.

*/

-- lets create a table holder for our results.

DECLARE  @t_sizes_BS TABLE ( 

                                                [Name]                                 VARCHAR(60)

                          ,         [Rows]                  INT

                          ,         [Reserved]              VARCHAR(60)

                          ,         [Data]                  VARCHAR(60)

                          ,         [Index_Size]            VARCHAR(60)

                          ,         [Unused]                VARCHAR(60)

  )

 

 

DECLARE @tab_name VARCHAR(100)

DECLARE csr_tab_sizes CURSOR  FOR

-- lets get the tables together with their schema. Leaving out schema names may result with sql server throwing an error

 SELECT table_Schema+ '.'+TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

OPEN csr_tab_sizes

FETCH NEXT FROM csr_tab_sizes INTO @tab_name

WHILE @@fetch_status = 0

BEGIN

INSERT INTO @t_sizes_BS

EXEC sp_spaceused  @tab_name

FETCH NEXT FROM csr_tab_sizes INTO @tab_name

END

CLOSE csr_tab_sizes

DEALLOCATE csr_tab_sizes

 SELECT   DISTINCT    [Name]

                        ,     [Rows]

                        ,     CAST(REPLACE(Reserved,'KB','') AS INT) Reserved

                        ,     CAST(REPLACE(Data,'KB','') AS INT) Data

                        ,     CAST(REPLACE(Index_Size,'KB','') AS INT) Index_Size

                        ,     CAST(REPLACE(Unused,'KB','') AS INT)      Unused

                        ,     CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE()))) AS 'AsOfToday'

FROM @t_sizes_BS

ORDER BY CAST([Rows] AS INT) DESC,CAST(REPLACE(Reserved,'KB','') AS INT) DESC