SQL Server: List Tables by Disk Space Size
List Tables by Disk Space Size in SQL Server
DBMS administrators often have to manage databases of several GBs, even the To. When a DB expands exponentially and without apparent explanation, the simplest is to look at which table occupies the most space. A simple query will give us the answer, to avoid having to check each of the hundreds of tables that can use business applications, an ERP or accounting software.
It is a question here of seeing the size occupied by the tables of the database in the MSSQL format, that is to say contained in the file MDF. The log of this DB is in the LDF file but it is not the subject of this guide.
To complete this tutorial, the free Microsoft SQL Server Management Studio software was used because it is the official utility for managing a Microsoft SQL Server database server. However, another program can be used to output information from a SQL Server database.
SQL Server query to list tables by size, number of rows, and disk space
- Open the Microsoft SQL Server Management Studio software or any other tool that allows you to run queries against a SQL Server database.
2. Log in with an SQL user account or Windows authentication.
3. Right-click on the database and choose New Query .
- In the empty field, type the following query:
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 to 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 UsedSpaceKB DESC
- Click on the ” Run ” button to start the search.
The result is displayed with a list of all the tables in the DB, sorted by ” UsedSpaceKB ” so the space used in KB.
We can now target searches to reduce the size of the largest table and reduce the disk space used by the MDF file of the SQL Server database.