How to find size of table in MSSQL?

Everyone would have come across a scenario where there is a need to find the Size of the table in MSSQL. There are multiple ways to find size of table in MSSQL.

How to find Size of table in MSSQL using the stored procedure?

sp_spaceused 'TABLE_NAME'
sp_spaceused '[dbo].[Blobs]'

Once the script is executed the output structure will be shown as below screen shot.

Column Details Explanation goes below.

Name: Provides the name of the table
Rows: Provides the total no of records in the table
Reserved: Provides the total space allocated to objects in the database.
Data: Provides the total space used by the data.
Index: Total amount of space used by the indexes.
Unused: Total amount of space reserved for objects in Database but not used yet.

The other way to find out the table size is through SQL Management Studio Interface.

Step 1. Connect to your database from SQL management studio
Step 2. Right click on the table which you need to find the size and click on properties.
Step 3. In the table property window select storage option as shown in the screenshot to find the table size and details

The final option is to write your own script and get the size. Find the below SQL snippet which returns all the tables size in your current database.

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
	t.NAME = 'blobs' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name

To find the size of all the tables in the current database use the below script. Not the where the condition is modified to get all the tables here.

 SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name