List tables and disk space in SQL Server database can be very convenient especially to analyze the disk usage and the free disk space used by each table. Three different ways, but similar are presented here to display SQL Server tables and disk space used by each table. The third solution shows the number of lines for every table.
How to list tables and disk space in SQL Server ?
Check our these three SQL queries to display the list of SQL Server tables:
- Using the information_schema.tables table
- Querying the sys.tables table
- Using the sys.tables table joined to indexes and partitions
1. First query to list all SQL Server tables using the information_schema.tables system tables
This first approach solution uses the data stored in information_schema schema about the tables. Use the information_schema.tables system table.
SELECT * FROM information_schema.tables WHERE table_type='base table'
2. Second query to list tables uses the sys.tables system tables
-- Use the sys.tables system table SELECT * FROM sys.tables
3. Get the list of all table from SQL Server database with row count and used space per table
This third query is using different system tables, namely sys.tables, sys.indexes, sys.partitions, sys.allocation_units and sys.schemas. It displays the schema name, table name, row counts and space (total, used and unused space) for each table in the SQL Server database.
Use sys.tables, sys.indexes, sys.partitions, sys.allocation_units and sys.schemas to display the line number, and also the size of the tables.
select sch.name as SchemaName, tab.name as TableName, par.rows as RowCounts, sum(alc.total_pages) * 8 as TotalSpace, sum(alc.used_pages) * 8 as UsedSpace, (sum(alc.total_pages) - sum(alc.used_pages)) * 8 as UnusedSpace FROM sys.tables tab INNER JOIN sys.indexes ind ON tab.object_id = ind.object_id INNER JOIN sys.partitions par ON ind.object_id = par.object_id and ind.index_id = par.index_id INNER JOIN sys.allocation_units alc ON par.partition_id = alc.container_id LEFT OUTER JOIN sys.schemas sch ON tab.schema_id = sch.schema_id GROUP BY tab.name, sch.name, par.rows ORDER BY 1,2
To go further, usually the goal is to check and free up space in a database. For example because the server hard drive is getting full, then this query allows you to know the last time a table was accessed or updated. It’s very useful before deletion. For instance if a table have not been access over the last three years, it might not be necessary anymore.
The disk space usage in SQL Server is a classical maintenance topic
Indeed any database grows, because the log grows, the data grows, the log file grows, the backup file grows, etc.
To fix this it’s mandatory to schedule maintenance scripts to clean the log tables and regularly delete the obsolete data, like for example a 5 years rolling period to keep on line. For compliance legal reasons, depending on the business area, the data might need to be kept and backed-up more years.