How to Check the Size of Tables in MSSQL Server

Are you wondering how to find out the size of tables in your MSSQL Server database? It's a common question that many database administrators and developers have. Knowing the size of your tables can help you better understand the storage requirements of your database and optimize its performance. In this article, we will guide you through the process of checking the size of tables in MSSQL Server.

Using SQL Server Management Studio (SSMS)

SQL Server Management Studio (SSMS) is a powerful tool that allows you to manage and monitor your MSSQL Server databases. To check the size of tables using SSMS, follow these steps:

1. Open SQL Server Management Studio and connect to your database server.
2. Expand the Databases folder and navigate to the database containing the tables you want to check.
3. Right-click on the database, select Reports, then Standard Reports, and finally click on Disk Usage by Table.

This report will show you the size of each table in the database, including the data and index size. You can use this information to identify which tables are taking up the most space in your database.

Using Transact-SQL Query

If you prefer using queries to retrieve information from your MSSQL Server database, you can use Transact-SQL to check the size of tables. Here's a simple query you can run:

1SELECT 
2    t.NAME AS TableName,
3    s.Name AS SchemaName,
4    p.rows AS RowCounts,
5    SUM(a.total_pages) * 8 AS TotalSpaceKB 
6FROM 
7    sys.tables t
8INNER JOIN      
9    sys.indexes i ON t.OBJECT_ID = i.object_id
10INNER JOIN 
11    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
12INNER JOIN 
13    sys.allocation_units a ON p.partition_id = a.container_id
14LEFT OUTER JOIN 
15    sys.schemas s ON t.schema_id = s.schema_id
16GROUP BY 
17    t.Name, s.Name, p.Rows
18ORDER BY 
19    TotalSpaceKB DESC

By executing this query, you will get a list of tables in your database along with their row counts and total space in kilobytes. This can be useful for identifying large tables that may require optimization.

Third-Party Tools

Alternatively, you can use third-party tools such as ApexSQL Analyze or Redgate SQL Monitor to easily view and analyze the size of tables in your MSSQL Server database. These tools provide graphical representations and detailed reports that can help you visualize the storage usage of your tables.

Checking the size of tables in your MSSQL Server database is an important task that can provide valuable insights into the storage requirements and performance of your database. By following the methods outlined in this article, you can effectively monitor and optimize the size of tables in your database.