Determining Table Sizes of Your DB in SQL 2005

I currently have a SQL 2005 database that seems larger than it should be.  It’s running about 5 gig, which is not very big, but once you add in all backups and such it is taking up too much space for my Virtual Server.  In SQL 2000 there was a HTML based page you could open to determine the size of you tables in number of rows and in KBs.  This page is nowhere to to be found in SQL 2005.  I did some research and came accross a great article on how to get this information here.  I will also detail how I handled it below:

First I created a table in my DB named TableSizes.  Here is the script for that:

CREATE TABLE [dbo].[TableSizes](

            [tablename] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

            [rows] [int] NULL,

            [reserved] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

            [reserved_int] [int] NULL CONSTRAINT [DF__TableSize__reser__33928E3B]  DEFAULT ((0)),

            [data] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

            [data_int] [int] NULL CONSTRAINT [DF__TableSize__data___3486B274]  DEFAULT ((0)),

            [index_size] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

            [index_size_int] [int] NULL CONSTRAINT [DF__TableSize__index__357AD6AD]  DEFAULT ((0)),

            [unused] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

            [unused_int] [int] NULL CONSTRAINT [DF__TableSize__unuse__366EFAE6]  DEFAULT ((0))

) ON [PRIMARY]

Next I created a the stored proc similar to the one in the article I mentioned above.  Except instead of using a temp table I’m storing the data in my TableSizes table.  I don’t have anything against temp tables I just like the idea of that data being saved of so I can use it later for reporting and such.  Here is the stored proc:

CREATE PROCEDURE dbo.spPopulateTableSize

AS

 

truncate table TableSizes

 

-- Populate the temp table...

EXEC sp_MSforeachtable @command1=

         "INSERT INTO TableSizes

           ([tablename],[rows],[reserved],[data],[index_size],[unused])

          EXEC sp_spaceused '?'"

  

-- Strip out the " KB" portion from the fields

UPDATE TableSizes SET

   [reserved_int] = CAST(SUBSTRING([reserved], 1,

                             CHARINDEX(' ', [reserved])) AS int),

   [data_int] = CAST(SUBSTRING([data], 1,

                             CHARINDEX(' ', [data])) AS int),

   [index_size_int] = CAST(SUBSTRING([index_size], 1,

                             CHARINDEX(' ', [index_size])) AS int),

   [unused_int] = CAST(SUBSTRING([unused], 1,

                             CHARINDEX(' ', [unused])) AS int)

  

-- Return the results...

SELECT * FROM TableSizes order by tablename

So now I can call this SP anytime I want and get a great view of the tables, and thier sizes, in my DB.  Hope this helps!

Posted in |

2 comments:

  1. Joe Says:

    Another way to do this:


    declare @name varchar(50)
    Declare UserTables cursor READ_ONLY For
    select name from sysobjects where xtype = 'u'
    Open UserTables
    Fetch Next from UserTables into @name
    While @@Fetch_Status = 0
    Begin
    exec sp_spaceused @name
    Fetch Next from UserTables into @name
    End
    CLOSE UserTables
    DEALLOCATE UserTables

  2. Anonymous Says:

    declare @name varchar(50)
    Declare UserTables cursor READ_ONLY For
    select name from sysobjects where xtype = 'u'
    Open UserTables
    Fetch Next from UserTables into @name
    While @@Fetch_Status = 0
    Begin
    exec sp_spaceused @name
    Fetch Next from UserTables into @name
    End
    CLOSE UserTables
    DEALLOCATE UserTables