List all the tables and used space in SQL Server database

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.

 

3 queries to display the list of SQL Server tables:

  1. Using information_schema.tables table
  2. Using sys.tables table
  3. Using sys.tables table joined to indexes, partitions, allocation_units and schemas ones

 

1. List all SQL Server tables with 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. List all SQL Server tables with 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

 

 

Be the first to comment

Leave a Reply