sql server list table sizes with code examples

SQL Server is a relational database management system (RDBMS) developed by Microsoft. It stores data in a structured manner and allows users to retrieve and manipulate data using SQL (Structured Query Language). One of the most common tasks in managing a SQL Server database is to check the size of individual tables, as well as the database as a whole.

In this article, we will explore various methods of getting the size of tables in SQL Server, ranging from simple queries to advanced techniques. We will also provide code examples along the way.

Method 1: Using SSMS (SQL Server Management Studio)

SQL Server Management Studio (SSMS) is a graphical tool that allows you to easily connect to SQL Server databases and perform various tasks. One of these tasks is to view the size of tables.

To view the size of a table in SSMS, follow these steps:

1. Open SSMS and connect to your SQL Server instance.
2. Expand the database you want to check.
3. Expand the Tables folder.
4. Right-click on the table you want to check and select Properties.
5. In the Table Properties dialog box, click on Storage.

In the Storage page, you can see the table size in kilobytes (KB), as well as the number of rows and the number of pages.

Method 2: Using sp_spaceused Stored Procedure

SQL Server provides a built-in stored procedure called sp_spaceused that returns information about the disk space used by a database, a table, or an index. To use this stored procedure to retrieve the size of a table, follow these steps:

1. Open SSMS and connect to your SQL Server instance.
2. Open a new query window.
3. Type the following code:

EXEC sp_spaceused 'tablename'

Replace tablename with the name of the table you want to check.
4. Execute the query.

The stored procedure returns the name of the table, the number of rows, the reserved space (total space allocated to the table), the data space (actual space used by the table), the index space (space used by indexes), and the unused space.

Method 3: Using sys.dm_db_partition_stats Dynamic Management View

SQL Server provides several dynamic management views (DMVs) that allow you to monitor the state of your SQL Server instance. One of these DMVs is sys.dm_db_partition_stats, which provides information about the size and number of partitions for each table and index in the database.

To use this DMV to retrieve the size of a table, follow these steps:

1. Open SSMS and connect to your SQL Server instance.
2. Open a new query window.
3. Type the following code:

SELECT
t.name AS TableName,
SUM(ps.reserved_page_count) * 8 AS TotalSizeKB,
SUM(ps.used_page_count) * 8 AS UsedSizeKB,
SUM(ps.reserved_page_count – ps.used_page_count) * 8 AS UnusedSizeKB
FROM
sys.dm_db_partition_stats AS ps
INNER JOIN sys.tables AS t ON ps.object_id = t.object_id
INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id
WHERE
s.name = 'dbo' — replace with the schema name
AND t.name = 'tablename' — replace with the table name
GROUP BY
t.name
ORDER BY
t.name

Replace dbo with the name of the schema containing the table you want to check, and tablename with the name of the table.
4. Execute the query.

The query returns the name of the table, the total size in KB, the used size in KB, and the unused size in KB.

Method 4: Using sp_MSforeachtable System Stored Procedure

SQL Server provides a system stored procedure called sp_MSforeachtable that executes a Transact-SQL statement against each table in a database. You can use this stored procedure to retrieve the size of all tables in a database.

To use this stored procedure to retrieve the size of all tables in a database, follow these steps:

1. Open SSMS and connect to your SQL Server instance.
2. Open a new query window.
3. Type the following code:

EXEC sp_MSforeachtable 'EXEC sp_spaceused ''?'''

4. Execute the query.

The stored procedure executes the sp_spaceused stored procedure against each table in the database and returns the results in separate result sets.

Method 5: Using PowerShell

PowerShell is a scripting language developed by Microsoft for automating administrative tasks in Windows environments. You can use PowerShell to connect to SQL Server and retrieve information about the size of tables.

To use PowerShell to retrieve the size of a table, follow these steps:

1. Open PowerShell.
2. Type the following code:

$Servername = 'servername'
$DatabaseName = 'databasename'
$Tablename = 'tablename'
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$Servername;Database=$DatabaseName;Integrated Security=True"
$SqlConnection.Open()
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "EXEC sp_spaceused '$Tablename'"
$SqlCmd.Connection = $SqlConnection
$Reader = $SqlCmd.ExecuteReader()
$Table = New-Object System.Data.DataTable
$Table.Load($Reader)
$Table

Replace servername with the name of your SQL Server instance, databasename with the name of the database containing the table you want to check, and tablename with the name of the table.
3. Execute the code.

The PowerShell script connects to SQL Server, executes the sp_spaceused stored procedure against the specified table, and returns the results in a DataTable object.

Conclusion

In this article, we have explored various methods of getting the size of tables in SQL Server, ranging from simple queries to advanced techniques. We have also provided code examples to help you retrieve the size of tables in your own SQL Server databases. Whether you prefer to use a graphical tool like SSMS, a built-in stored procedure like sp_spaceused, a dynamic management view like sys.dm_db_partition_stats, or a scripting language like PowerShell, you can easily obtain the information you need to manage your SQL Server databases effectively.

let's dive deeper into the methods we discussed for listing table sizes in SQL Server.

Method 1: Using SSMS (SQL Server Management Studio)

SSMS is a powerful tool that offers several ways to view the size of tables in SQL Server. In addition to the method we mentioned in the previous section, you can also use the Object Explorer Details window to view the sizes of all tables in a database. To do this, follow these steps:

1. Open SSMS and connect to your SQL Server instance.
2. Open Object Explorer by clicking on the icon in the left-hand pane.
3. Expand the Databases folder.
4. Right-click on the database you want to check and select Object Explorer Details.
5. In the Object Explorer Details window, you can see the size of each table in the Size (KB) column.

This method is useful if you want to quickly get an overview of the sizes of all tables in a database and compare their sizes.

Method 2: Using sp_spaceused Stored Procedure

The sp_spaceused stored procedure is a convenient way to retrieve information about the disk space used by a table in SQL Server. However, it has some limitations. For example, it only shows the space used by the table's data and indexes, not the space used by the table's metadata, such as triggers and constraints. It also does not take into account the space used by related objects, such as stored procedures and views.

You can modify the output of the sp_spaceused stored procedure by passing a parameter called @updateusage. When you set this parameter to 'true', SQL Server updates the space usage statistics for the specified table before returning the results. This ensures that the results are up-to-date, but it can also increase the execution time of the stored procedure.

Method 3: Using sys.dm_db_partition_stats Dynamic Management View

The sys.dm_db_partition_stats dynamic management view provides detailed information about the partition sizes and locations for all tables and indexes in a database. You can use this view to retrieve the size of a specific table or index, or to find the largest tables in a database.

One limitation of this method is that it only shows the size of the table on disk, not the actual size of the data in the table. This is because the space allocated to a table may include empty pages and unused space.

Method 4: Using sp_MSforeachtable System Stored Procedure

The sp_MSforeachtable system stored procedure is a powerful tool that allows you to execute a Transact-SQL statement against all tables in a database. This can be useful for tasks such as rebuilding indexes, updating statistics, or backing up data.

One disadvantage of this method is that it can be slow, especially on large databases with many tables. It can also generate a lot of output, which can be difficult to parse and analyze.

Method 5: Using PowerShell

PowerShell is a versatile scripting language that allows you to automate tasks in SQL Server and other Microsoft products. You can use PowerShell to connect to SQL Server, retrieve information about databases and tables, and perform administrative tasks.

One advantage of this method is that PowerShell provides powerful scripting capabilities, allowing you to automate complex tasks and generate reports. It also integrates well with other Microsoft technologies, such as Active Directory and Azure.

Conclusion

Listing table sizes in SQL Server is an important task for database administrators and developers. By using the methods we discussed in this article, you can easily retrieve information about the disk space used by tables in SQL Server. Each method has its advantages and limitations, so it's important to choose the method that best fits your needs. Whether you prefer to use a graphical tool like SSMS, a built-in stored procedure like sp_spaceused, a dynamic management view like sys.dm_db_partition_stats, or a scripting language like PowerShell, you have the power to gain insights into your SQL Server databases and optimize their performance.

Popular questions

1. What is SQL Server?

SQL Server is a relational database management system (RDBMS) developed by Microsoft. It stores data in a structured manner and allows users to retrieve and manipulate data using SQL (Structured Query Language).

2. What are some of the methods for listing table sizes in SQL Server?

There are several methods for listing table sizes in SQL Server, including using SQL Server Management Studio (SSMS), the sp_spaceused stored procedure, the sys.dm_db_partition_stats dynamic management view, the sp_MSforeachtable system stored procedure, and using PowerShell.

3. Can you explain how to use the sp_spaceused stored procedure to retrieve the size of a table in SQL Server?

To use the sp_spaceused stored procedure to retrieve the size of a table in SQL Server, you can execute the following code in a query window:

EXEC sp_spaceused 'tablename'

Replace 'tablename' with the name of the table you want to check. The stored procedure will return the name of the table, the number of rows, the reserved space (total space allocated to the table), the data space (actual space used by the table), the index space (space used by indexes), and the unused space.

4. What is a dynamic management view in SQL Server?

A dynamic management view (DMV) is a virtual table that provides real-time information about the state of a SQL Server instance. DMVs can be used to monitor system performance, diagnose problems, and tune the database.

5. How can PowerShell be used to list table sizes in SQL Server?

PowerShell can be used to connect to SQL Server, retrieve information about databases and tables, and perform administrative tasks. To list the size of a table in PowerShell, you can execute the following code:

$Servername = 'servername'
$DatabaseName = 'databasename'
$Tablename = 'tablename'
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$Servername;Database=$DatabaseName;Integrated Security=True"
$SqlConnection.Open()
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "EXEC sp_spaceused '$Tablename'"
$SqlCmd.Connection = $SqlConnection
$Reader = $SqlCmd.ExecuteReader()
$Table = New-Object System.Data.DataTable
$Table.Load($Reader)
$Table

Replace 'servername' with the name of your SQL Server instance, 'databasename' with the name of the database containing the table you want to check, and 'tablename' with the name of the table. The PowerShell script will connect to SQL Server, execute the sp_spaceused stored procedure against the specified table, and return the results in a DataTable object.

Tag

Database sizing