Stored Procedure to Display Spaced Used for all Tables in any SQL Server Database

Using the technique I discussed in this post, the following SQL Server stored procedure can be used to display the space used for all tables in a specified database. Put the stored procedure into whatever database you use for your Utilities database, then run the procedure from anywhere, passing in the name of the database for which to display table usage information.

CREATE PROCEDURE p_show_spaceused_all_tables(@dbname VARCHAR(255) = NULL)
AS
BEGIN

DECLARE @sql VARCHAR(MAX)

SET @sql = '
SET NOCOUNT ON 

CREATE TABLE #t 
( 
    [name] NVARCHAR(128),
    [rows] CHAR(11),
    reserved VARCHAR(18), 
    data VARCHAR(18), 
    index_size VARCHAR(18),
    unused VARCHAR(18)
) 

INSERT #t EXEC sp_msForEachTable ''EXEC sp_spaceused ''''?'''''' 

SELECT *
FROM   #t
UNION 
SELECT ''~~GRAND TOTAL'' AS name, null AS rows,
       CAST(SUM(CAST(REPLACE(reserved, ''KB'', '''') AS INT)) AS VARCHAR(18)) + '' KB'' AS reserved,
       CAST(SUM(CAST(REPLACE(data, ''KB'', '''') AS INT)) AS VARCHAR(18)) + '' KB'' AS data,
       CAST(SUM(CAST(REPLACE(index_size, ''KB'', '''') AS INT)) AS VARCHAR(18)) + '' KB'' AS index_size,
       CAST(SUM(CAST(REPLACE(unused, ''KB'', '''') AS INT)) AS VARCHAR(18)) + '' KB'' AS unused
FROM   #t
ORDER BY name

DROP TABLE #t'


IF @dbname IS NOT NULL
BEGIN
    SET @sql = 'USE ' + @dbname + ' ' + @sql
END

EXEC (@sql)
END

Note: The contents of the “@sql” SQL Query used inside this stored procedure is from Alexander Grob’s article “How to Obtain the Size of All Tables in a SQL Server Database“. My contribution was simply to turn that query into a stored procedure that could access any database and to add the “Grand Total” values.