Query to list all SQL Server tables

To audit a MSSQL database and its tables, it is common to want the list of tables in the form of a table or an Excel spreadsheet. This SQL query allows you to list all the tables in a SQL Server database and display the number of rows and size for each table. SQL Server developers also often look for tables, for example for impact analysis.

Thus, all the solutions presented in this article use system tables, such as information_schema.tables , sys.tables, sys.indexes, sys.partitions, sys.allocation_units or sys.schemas. Indeed it is useful to quickly list all the tables for an audit for example, whether it is technical or functional.

SQL query to list all tables in a SQL Server database

To begin with, these 3 queries allow you to display the information in different ways.

1. The first is a simple list of tables.
2. Then the second query displays useful information such as the object_id column and the create_date and modify_date.
3. The third query displays the schema, the number of rows and the disk space used by each table.

Display all tables using the information_schema.tables system table

The first option, for example, you can use the information_schema.table system table, as follows:

SELECT *
FROM information_schema.tables
WHERE table_type='BASE TABLE';

List all the tables from the system table sys.tables

For the second option, you can see in the result of the query that the type of the tables stored in the column [type_desc] is “USER_TABLE”.

SELECT *
FROM sys.tables;

SQL query to display all SQL Server tables and their size

The third one is to use multiple SQL Server system tables such as sys.tables, sys.indexes, sys.partitions, sys.allocation_units and sys.schemas.

These tables are useful for displaying the list of tables. They are also used to display the number of rows in each table and the disk space used. For example, this query displays all tables with their number of rows sorted in descending order. This query is useful to find the size of the largest tables in a SQL Server database.

SELECT
sch.name AS SCHEMANAME,
tab.name AS TABLENAME,
par.rows AS ROWCOUNTS,
SUM(alc.total_pages) * 8 AS TOTAL_SPACE,
SUM(alc.used_pages) * 8 AS USED_SPACE,
(SUM(alc.total_pages) - SUM(alc.used_pages)) * 8 AS UNUSED_SPACE
FROM sys.tables tabSELECT
	sch.name AS SCHEMANAME,
	tab.name AS TABLENAME,
	par.rows AS ROWCOUNTS,
	SUM(alc.total_pages) * 8 AS TOTAL_SPACE,
	SUM(alc.used_pages) * 8 AS USED_SPACE,
	(SUM(alc.total_pages) - SUM(alc.used_pages)) * 8 AS UNUSED_SPACE
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 par.rows DESC;

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 par.rows DESC;

Notes

• The keyword “ORDER BY par.rows DESC;” is used to sort the list of SQL Server tables by their number of rows, in a descending order.
• To sort by schema and then by table name, use “ORDER BY 1,2” or “ORDER BY sch.name, tab.name; ” instead.
• The size of the tables is noted in KiloBytes or KB.

If you are still new to SQL Server and want to create a table, here is how to create a SQL Server table and insert a few rows of data.

It is possible to display another list of MS SQL tables with more focus on disk space available.

SQL Server table size and disk space