SQL Server table size and disk space

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.

Get all SQL Server table names with table size and disk space used

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 
	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 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.

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 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 the SQL Server tables size with the information_schema.tables system view

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.

SELECT 
	TABLE_CATALOG, 
	TABLE_SCHEMA, 
	TABLE_NAME, 
	TABLE_TYPE
FROM	information_schema.tables
WHERE	table_type='base table';

List all the SQL tables from the sys.tables system table

The last query 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
SELECT	*
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.

Empty the SQL Server transaction log and fix error 9002