List tables and disk space in SQL Server

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:

  1. Using the information_schema.tables table
  2. Querying the sys.tables table
  3. 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.

Be the first to comment

Leave a Reply

Your email address will not be published.


*