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.

SQL Table Size Script

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

SQL Table Size Object Explorer

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

0 Shares
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like
XOR in Python

XOR in Python

XOR Operator in Python is also known as “exclusive or”  that compares two binary numbers bitwise if two bits are…
How To

How to configure Git proxy?

If you are working in the corporate company all the internet requests usually goes through the corporate firewall.…