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
Leave a Reply

Your email address will not be published.

Sign Up for Our Newsletters

Get notified of the best deals on our WordPress themes.

You May Also Like
Convert String To Float In Python

Convert String to Float in Python

Table of Contents Hide Convert string to float in Pythonfloat() functionExample 1 – Convert string to floatExample 2 – Default float conversion without any input parameterExample 3 – Convert a…
View Post
How To Check If A File Exists In Python

How to check if a file exists in Python?

Table of Contents Hide Python check if a file exists using OS ModuleUsing os.path.exists()Using os.path.isfile()Using os.path.isdir()Using pathlib.Path.exists() When you perform a file operation such as reading from a file or…
View Post
Python Read Text File

Python Read Text file

Table of Contents Hide Steps to Read Text File in Python Python open() function Methods for Reading file contentsPython close() functionExamples for Reading a Text file in Python Example 1 – Read…
View Post