FREQUENTLY ASK QUESTIONS

SQL Server Management Studio can display information about the size of individual tables in the form of a beautiful report. However, how to advise if you want to view this information using your own application.

The solution is the TableStatistics stored procedure presented below

Its output is a "resultset", which contains the following data for each table in the database:
Name - table name
Rows - num rows
Reserved - data size reserved for each table (kB)
Data - real data size (kB)
IndexSize - data size of indexes (kB)
Unused - free data size (kB)

Create procedure

CREATE PROCEDURE [dbo].[TableStatistics]

AS 

BEGIN 

    SET NOCOUNT ON

    -- Create the temporary table

    CREATE TABLE #TableStatistics (
       name              nvarchar(100),
       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 table

    EXEC sp_MSforeachtable @command1="INSERT INTO #TableStatistics (name,rows,reserved,data,index_size,unused) EXEC sp_spaceused '?'"

    -- Return converted values

    SELECT 
        name AS Name,
        rows AS Rows,
        CAST(SUBSTRING(reserved, 1, CHARINDEX(' ', reserved)) AS int) AS Reserved,
        CAST(SUBSTRING(data, 1, CHARINDEX(' ', data)) AS int) AS Data,
        CAST(SUBSTRING(index_size, 1, CHARINDEX(' ', index_size)) AS int) AS IndexSize,
        CAST(SUBSTRING(unused, 1, CHARINDEX(' ', unused)) AS int) AS Unused

    FROM #TableStatistics ORDER BY Name

    -- Delete the temporary table

    DROP TABLE #TableStatistics

END 

Spuštění procedury

Exec TableStatistics