Control - How to determine the size of each SQL database table

Explanation of SQL

This set of SQL queries will create a stored procedure that will generate a list of tables with their relevant sizes. An excel spreadsheet has been provided for additional formatting of the data.

Risk of Data Corruption if Run Improperly

None. This is a selection query and no data is modified in the running of it.

SQL


Step 1: Create the Stored Procedure GetAllTableSizes

Download Link: Control - SQL Database Table Size Breakdown.sql

CREATE PROCEDURE GetAllTableSizes
AS
/*
    Obtains spaced used data for ALL user tables in the database
*/
DECLARE @TableName VARCHAR(100)    --For storing values in the cursor
 
--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
FOR
SELECT [name]
FROM dbo.sysobjects
WHERE  OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY
 
--A procedure level temp table to store the results
CREATE TABLE #TempTable
(
    tableName VARCHAR(100),
    numberofRows VARCHAR(100),
    reservedSize VARCHAR(50),
    dataSize VARCHAR(50),
    indexSize VARCHAR(50),
    unusedSize VARCHAR(50)
)
 
--Open the cursor
OPEN tableCursor
 
--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName
 
--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
    --Dump the results of the sp_spaceused query to the temp table
    INSERT  #TempTable
        EXEC sp_spaceused @TableName
 
    --Get the next table name
    FETCH NEXT FROM tableCursor INTO @TableName
END
 
--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor
 
--Select all records so we can use the reults
SELECT *
FROM #TempTable
ORDER BY CAST(numberofrows AS INT) DESC
 
--Final cleanup!
DROP TABLE #TempTable
 
GO

Step 2: Execute the Stored Procedure GetAllTableSizes to get the table size results.


EXEC GetAllTableSizes
 

Step 3: Paste the query results into the first 5 columns of the excel spreadsheet below. The last 4 columns will calculate the table sizes in more usable numbers in megabytes.


Download Link:

Version Information

  • Entered : 10/14/2009
  • Version : 1.0

Related SQLs