Give me a MSSQL query to list the table sizes of a database
- User
Give me a MSSQL query to list the table sizes of a database
- Assistant
To list the table sizes in an MSSQL database, you can use a query that utilizes the
sys.tablessystem catalog view, along with thesys.indexes,sys.partitions, andsys.allocation_unitssystem views to calculate the size for each table.Here's an example of a query that will list the table sizes in an MSSQL 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 NOT LIKE 'dt%' AND i.OBJECT_ID > 255 AND i.index_id <= 1 GROUP BY t.Name, s.Name, p.Rows ORDER BY TotalSpaceKB DESC, t.NameHere's a breakdown of what this query does:
t.NAMEprovides the name of the table.s.Nameprovides the schema name where the table resides.p.rowsprovides the count of rows in the table.SUM(a.total_pages) * 8 AS TotalSpaceKBcalculates the total space allocated for the table in kilobytes.SUM(a.used_pages) * 8 AS UsedSpaceKBcalculates the space actually used by the table in kilobytes.(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKBcalculates the unused space for the table in kilobytes.
The
* 8factor converts pages to kilobytes since one page in SQL Server is typically 8 KB.The
GROUP BYclause is used to aggregate the data per table, and theORDER BYclause is sorting the results by the total space used in descending order, so the largest tables will be listed first. TheWHEREclause filters out system
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.