How to display all SQL Server tables size and disk space ?

How to display all SQL Server table names with table size and disk space used with one query ?

Listing SQL Server table size and disk space can be very convenient specially to analyse the disk usage for 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 also shows the number of lines for every table. Check out these SQL Server queries to display the list of SQL Server tables and their size in different ways.

This third query is using different system tables joined together, namely sys.tables, sys.indexes, sys.partitions, sys.allocation_units and sys.schemas. It displays the schema name, the table name, the row counts and the disk space (total, used and unused space) for each table in a SQL Server database.

select as SchemaName, 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 

To go further, usually the goal is to check and free up disk space on the server hosting the database. Typically when the server hard drive is getting full, then this query displays the last time a table was accessed or updated. It’s very useful before deletion. For instance, if a table have not been accessed over the last three years, it might not be necessary anymore.

SQL Server disk space usage 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 five 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.

List all SQL Server tables size using system views

This approach is to use the data stored in the information_schema schema about the tables. The Microsoft SQL query uses the information_schema.tables system table to display all the attributes and metadata from the system database. It displays the four columns available in the tables system view:

  • TABLE_CATALOG is the database name.
  • TABLE_SCHEMA is the schema hosting the table, per default it is database owner (dbo).
  • TABLE_NAME is the name of the table.
  • TABLE_TYPE is the table type.

Note that we filter the query on the view to display only the base table type. Other table types can also be View.

FROM	information_schema.tables
WHERE	table_type='base table';

List all MS SQL tables using system tables

To display SQL Server tables size and disk space, you can also run this last query in SSMS. This one uses the sys.tables system table, it is certainly the source of the system view used in the first query. It shows much more information’s and metadata. Like for example the creation data, the modification date, and the object_id reusable in other system functions and procedures.

-- Use the sys.tables system table
FROM	sys.tables
WHERE	[type_desc] = 'USER_TABLE';

To go further in the SQL Server database maintenance topic, check out this short tutorial on how to empty the transaction log to free up disk space with a Shrink Database script.

Clear the SQL Server transaction log and fix the 9002 error

Leave a Comment

Your email address will not be published. Required fields are marked *