SQL Server list all the tables and disk space

List all existing tables from an 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 SQL Server tables and disk space used ?

3 queries to display the list of SQL Server tables:

  1. Using the information_schema.tables table
  2. Queryin the sys.tables table
  3. Last but not least by using sys.tables table joined to indexes, partitions, allocation_units and schemas ones to also display the row count per table.

 

 

1. First query to list all SQL Server tables using the information_schema.tables system tables

This solution uses the system tables stored in information_schema and sys schemas.

-- Use the information_schema.tables system table
SELECT *
FROM information_schema.tables
WHERE table_type='base table'

2. Second query to list all SQL Server tables using 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 various system tables, namely sys.tables, sys.indexes, sys.partitions, sys.allocation_units and sys.schemas and 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 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



If the goal is free up space in a database because the server hard drive is getting full, then this query allows you to know the last time a table was accessed or updated. Useful before deletion.

Be the first to comment

Leave a Reply