How to Check Disk Space Usage for Tables in SQL Server Using T-SQL

When managing a database, understanding how much disk space each table is consuming is vital for optimization and capacity planning. While many users might be accustomed to checking table properties through the SQL Server Management Studio (SSMS) graphical interface, knowing how to perform the same task using T-SQL can be extremely beneficial, especially for automation and advanced database management tasks.

In this blog post, we will walk through a straightforward method to determine the disk space occupied by tables in SQL Server through T-SQL. This approach not only enhances your efficiency but also provides an opportunity to dive deeper into SQL scripting.

Overview of the Solution

We’ll utilize temporary tables coupled with the sp_spaceused system stored procedure to gather information about the various tables in your database. Here’s a breakdown of what we will accomplish:

1. Create temporary tables to hold size information.
2. Execute the sp_spaceused command to gather size data from all tables.
3. Parse the collected data into readable metrics.
4. Display the output sorted by reserved size.

Let’s get started!

Step 1: Create Temporary Tables

The first step is to define two temporary tables that will help us store the size details we’ll extract. In SQL Server, temporary tables are prefixed with a # sign.

CREATE TABLE #tmpSizeChar (
     table_name sysname ,
     row_count int,
     reserved_size varchar(50),
     data_size varchar(50),
     index_size varchar(50),
     unused_size varchar(50))                              

CREATE TABLE #tmpSizeInt (
     table_name sysname ,
     row_count int,
     reserved_size_KB int,
     data_size_KB int,
     index_size_KB int,
     unused_size_KB int)   

Explanation:

• #tmpSizeChar: Holds results in character format, which retains the size with units (e.g., MB, KB).
• #tmpSizeInt: Stores results in integer format to facilitate easy computation.

Step 2: Gather Size Data from Tables

We use the sp_msforeachtable stored procedure to run the sp_spaceused command on every table in the database. This command provides detailed information on each table’s space utilization.

SET NOCOUNT ON
INSERT #tmpSizeChar
EXEC sp_msforeachtable 'sp_spaceused ''?'''

Explanation:

• NOCOUNT ON: Prevents the message indicating the number of rows affected by a T-SQL statement from being returned, which improves performance for scripts with multiple statements.

Step 3: Parse Collected Data

Having gathered the data, we now need to transform the character data into integer values suitable for analysis.

INSERT INTO #tmpSizeInt (
        table_name,
        row_count,
        reserved_size_KB,
        data_size_KB,
        index_size_KB,
        unused_size_KB
        )
SELECT  [table_name],
        row_count,
        CAST(SUBSTRING(reserved_size, 0, PATINDEX('% %', reserved_size)) AS int) reserved_size,
        CAST(SUBSTRING(data_size, 0, PATINDEX('% %', data_size)) AS int) data_size,
        CAST(SUBSTRING(index_size, 0, PATINDEX('% %', index_size)) AS int) index_size,
        CAST(SUBSTRING(unused_size, 0, PATINDEX('% %', unused_size)) AS int) unused_size
FROM #tmpSizeChar

Explanation:

• This step extracts the numeric part of the reserved sizes and converts them into integers for reliable calculations. It essentially removes the characters (like KB, MB) from the size strings.

Step 4: Display the Results

Finally, we retrieve and display the summarized results from our temporary integer-sized table.

SELECT * FROM #tmpSizeInt
ORDER BY reserved_size_KB DESC

Explanation:

• This SQL command retrieves all records from #tmpSizeInt and orders them by the reserved_size_KB column, allowing you to see which tables consume the most disk space.

Conclusion

By following the steps outlined above, you now have a structured way to determine how much disk space a table is using in SQL Server with T-SQL. This method can be particularly useful for database administrators and developers looking to maintain optimal performance and keep track of resource usage across their SQL Server environments.

Now, you can replace the GUI clicks with a powerful command-line approach, fundamentally enhancing your database management skills!