Displaying the Sizes of Your SQL Server’s Database’s Tables


 

Determining the Space Used by a Database or a particular Table
SQL Server has a handy little system stored procedure named sp_spaceused that will return the space used by a database or by a particular table. To determine the size used by the database, simply run:

EXEC sp_spaceused [table]

Returning the Space Used for All Tables

The sp_MSforeachtable stored procedure is one of many undocumented stored procedures tucked away in the depths of SQL Server. A list of these handy stored procedures can be found at SQL Server 2000 Useful Undocumented Stored Procedures. In short, you can use sp_MSforeachtable like so:

EXEC sp_MSforeachtable @command1="command to run"

In the command to run put a ? where you want the table name to be inserted. For example, to run the sp_spaceused stored procedure for each table in the database, we’d use:

EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

This will execute EXEC sp_spaceused 'TableName' for each user table in the database.

Combining Multiple Result Sets Into a Single Result Set Using Temporary Tables

CREATE PROCEDURE dbo.TableSpaceUsed
AS

-- Create the temporary table...
CREATE TABLE #tblResults
(
   [name]   nvarchar(50),
   [rows]   int,
   [reserved]   varchar(18),
   [reserved_int]   int default(0),
   [data]   varchar(18),
   [data_int]   int default(0),
   [index_size]   varchar(18),
   [index_size_int]   int default(0),
   [unused]   varchar(18),
   [unused_int]   int default(0)
)


-- Populate the temp table...
EXEC sp_MSforeachtable @command1=
         "INSERT INTO #tblResults
           ([name],[rows],[reserved],[data],[index_size],[unused])
          EXEC sp_spaceused '?'"
   
-- Strip out the " KB" portion from the fields
UPDATE #tblResults 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 #tblResults

This stored procedure will return a single result set. The fields that end in _int are the integer representations of those fields that are returned with KB fields.

image

4GuysFromRolla.com – Displaying the Sizes of Your SQL Server’s Database’s Tables

About these ads

3 thoughts on “Displaying the Sizes of Your SQL Server’s Database’s Tables

  1. Bend the top 6″ inches of mesh outward to stop rabbits from getting over the top of the fence. Throughout your gardening trek, you should be able to familiarize yourself with all the plants in your garden. It will not be long before you have enough worms to be able to utilize their castings in your garden.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s