List All Tables Across All Databases in SQL Server

A table inventory that spans every database on an instance cannot be answered from one database context, because the table catalog lives inside each database. The pattern below reads the database list from sys.databases, builds one INSERT ... SELECT per online user database against that database's sys.tables and sys.schemas, runs the batch once and returns a single sorted result. A second script narrows the same inventory to a table name.

Purpose and Overview

SQL Server 2014 added is_memory_optimized to sys.tables, SQL Server 2016 added is_external, and SQL Server 2017 added is_node and is_edge for graph tables. A cross-database table inventory has therefore changed meaning over the years: the same view now lists disk-based tables, in-memory tables, external tables and graph tables side by side. The scripts here return the base columns that exist on every supported version and leave the version-specific flags as additions.

Two catalog objects carry the pattern. sys.databases returns one row per database on the instance, with name, database_id, state_desc and user_access_desc among its columns. sys.tables returns a row for each user table in the database it is queried from, and it inherits the columns of sys.objects, including create_date, modify_date and is_ms_shipped. Because sys.tables is per database, a three-part name such as [Sales].sys.tables is how one session reads another database's catalog without a USE statement.

sys.schemas is joined on schema_id so the output shows a readable schema name. Schema matters here because two databases often hold a dbo.Customer table, and applications that use Sales, HR or Staging schemas produce names that are ambiguous without it.

Two scripts follow. The first returns every user table in every online user database. The second collects the same list and then filters on a name fragment, which answers the question "which database holds the table called Customer" during a migration or a refactor.

Code Breakdown

Both scripts create a temporary table, build a dynamic batch from sys.databases, run it with sp_executesql and select from the temporary table. Run them from any database; the catalog reads use three-part names.

 1IF OBJECT_ID('tempdb..#AllTables') IS NOT NULL
 2    DROP TABLE #AllTables;
 3
 4CREATE TABLE #AllTables (
 5    database_name SYSNAME  NOT NULL,
 6    schema_name   SYSNAME  NOT NULL,
 7    table_name    SYSNAME  NOT NULL,
 8    create_date   DATETIME NOT NULL,
 9    modify_date   DATETIME NOT NULL
10);
11
12DECLARE @SQL NVARCHAR(MAX);
13
14SELECT @SQL = STRING_AGG(CAST(N'
15INSERT INTO #AllTables (database_name, schema_name, table_name,
16                        create_date,  modify_date)
17SELECT N''' + REPLACE(name, N'''', N'''''') + N''',
18       s.name,
19       t.name,
20       t.create_date,
21       t.modify_date
22FROM ' + QUOTENAME(name) + N'.sys.tables  AS t
23INNER JOIN ' + QUOTENAME(name) + N'.sys.schemas AS s
24        ON t.schema_id = s.schema_id
25WHERE t.is_ms_shipped = 0;' AS NVARCHAR(MAX)), N'')
26       WITHIN GROUP (ORDER BY name)
27FROM sys.databases
28WHERE state_desc = N'ONLINE'
29  AND name NOT IN (N'master', N'model', N'msdb', N'tempdb');
30
31EXEC sys.sp_executesql @SQL;
32
33SELECT database_name, schema_name, table_name, create_date, modify_date
34FROM   #AllTables
35ORDER BY database_name, schema_name, table_name;
36
37DROP TABLE #AllTables;

The search variant keeps the same collection step and changes only the final query:

 1IF OBJECT_ID('tempdb..#AllTables') IS NOT NULL
 2    DROP TABLE #AllTables;
 3
 4CREATE TABLE #AllTables (
 5    database_name SYSNAME  NOT NULL,
 6    schema_name   SYSNAME  NOT NULL,
 7    table_name    SYSNAME  NOT NULL,
 8    create_date   DATETIME NOT NULL
 9);
10
11DECLARE @TableSearch SYSNAME       = N'Customer';  -- partial or exact name
12DECLARE @SQL         NVARCHAR(MAX);
13
14SELECT @SQL = STRING_AGG(CAST(N'
15INSERT INTO #AllTables (database_name, schema_name, table_name, create_date)
16SELECT N''' + REPLACE(name, N'''', N'''''') + N''',
17       s.name,
18       t.name,
19       t.create_date
20FROM ' + QUOTENAME(name) + N'.sys.tables  AS t
21INNER JOIN ' + QUOTENAME(name) + N'.sys.schemas AS s
22        ON t.schema_id = s.schema_id
23WHERE t.is_ms_shipped = 0;' AS NVARCHAR(MAX)), N'')
24       WITHIN GROUP (ORDER BY name)
25FROM sys.databases
26WHERE state_desc = N'ONLINE'
27  AND name NOT IN (N'master', N'model', N'msdb', N'tempdb');
28
29EXEC sys.sp_executesql @SQL;
30
31SELECT database_name, schema_name, table_name, create_date
32FROM   #AllTables
33WHERE  table_name LIKE N'%' + @TableSearch + N'%'
34ORDER BY database_name, schema_name, table_name;
35
36DROP TABLE #AllTables;

Choosing which databases to read

state_desc = N'ONLINE' limits the list to databases that can answer a query. The state values also include RESTORING, RECOVERING, RECOVERY_PENDING, SUSPECT, EMERGENCY and OFFLINE, and none of those belong in the batch. The four system databases are excluded by name. master records the system-level information for the instance, model is the template for new databases, msdb is used by SQL Server Agent for scheduling alerts and jobs, and tempdb is the workspace for temporary objects. Excluding by name avoids depending on specific database_id values.

Row visibility in sys.databases is also permission-bound. A caller who does not own a database sees its row only with ALTER ANY DATABASE, VIEW ANY DATABASE or CREATE DATABASE in master, and the public role holds VIEW ANY DATABASE by default. A login whose access was tightened gets a shorter database list, so a missing database in the output can be a visibility result and not a sign the database is absent.

Building the batch with QUOTENAME and REPLACE

STRING_AGG joins one INSERT ... SELECT block per row from sys.databases into a single batch, in database-name order, so there is no cursor. The input is cast to NVARCHAR(MAX) because STRING_AGG otherwise limits its result to 4,000 characters for nvarchar input, which a few dozen databases would exceed. The older SELECT @SQL += ... form is not safe here: Microsoft documents that a variable assigned from its own value across many rows isn't guaranteed to be updated row by row, so blocks can go missing silently. STRING_AGG needs SQL Server 2017 or later. QUOTENAME(name) wraps the database name in square brackets and doubles any closing bracket, so names with spaces, hyphens or reserved words become valid delimited identifiers. Its input is limited to 128 characters, which matches the width of sysname.

The database_name value is written into the SQL text as a Unicode string literal. A name that contains a single quote would end that literal early, so REPLACE(name, N'''', N'''''') doubles each quote before the name goes into the text. The N prefix keeps non-ASCII database names intact.

Running the batch

sp_executesql requires @stmt to be a Unicode constant or variable, and @SQL is NVARCHAR(MAX), so it qualifies. The statement has the same database context behavior as EXECUTE, so the three-part names are what direct each INSERT at the right catalog.

The is_ms_shipped filter

is_ms_shipped is set on objects created by an internal SQL Server component. Filtering it to 0 returns the tables the application or the DBA created. Removing the predicate returns every user-table row in sys.tables, which is the right choice only when the goal is to see what features have created in each database.

Why the search runs after collection

The LIKE predicate sits in the last query, not in the dynamic text. The dynamic string stays free of search-term escaping, and the filter can be changed without rebuilding the batch. The cost is that every table is inserted before the filter applies. On an instance with many databases and tables, add the predicate inside the per-database block (AND t.name LIKE N'%...%') and escape the search value with REPLACE the same way the database name is escaped.

Key Benefits and Use Cases

  • One sorted result for the whole instance. Migration planning, audit work and clean-up all start from the same list of database, schema and table.
  • Finding a table by name. The search variant answers where a table lives before a rename, a drop or a consolidation.
  • Change tracking by date. create_date shows when a table appeared, and modify_date shows the last ALTER of the table or the last creation or alteration of one of its indexes.
  • Tolerates unusual database names. QUOTENAME and REPLACE handle spaces, brackets and quotes without per-name special cases.
  • Easy to extend. Extra columns from sys.tables, or joins to sys.partitions in the same block, add detail without changing the surrounding structure.

Performance Considerations

  • Catalog reads only. The batch reads metadata and no user data pages, so run time follows the number of databases and tables rather than data volume.
  • Result size follows object count. With 100 databases of 500 tables each, the final query returns 50,000 rows. Filter on database_name or add TOP when the full list is not needed.
  • String size. NVARCHAR(MAX) holds up to 2^31-1 characters (2 GB), and each database adds a few hundred characters of SQL, so the limit is not a practical concern.
  • Temporary table scope. #AllTables lives in tempdb and is visible only to the session that created it. Five narrow columns keep it small.
  • Permissions shape the output. Catalog views show only securables the caller owns or holds a permission on, so a low-privilege login receives a partial inventory. Run the script as a login with broad metadata access when completeness matters.
  • User access mode. user_access_desc reports MULTI_USER, SINGLE_USER or RESTRICTED_USER. Add a predicate on it when single-user databases should be left out.

Practical Tips

  • Add the version-specific flags where they exist. is_memory_optimized needs SQL Server 2014 or later, and is_external needs SQL Server 2016 or later. Add them to the temporary table and the SELECT list only on instances that have them, or the batch fails on older builds.
  • Count rows approximately. sys.partitions.rows is the approximate number of rows in a partition. Summing it for index_id values below 2 (0 is a heap, 1 is the clustered index) gives a per-table estimate to add beside the name.
  • Spot compression. sys.partitions.data_compression_desc reports NONE, ROW or PAGE for rowstore partitions and COLUMNSTORE or COLUMNSTORE_ARCHIVE for columnstore, which makes the same pattern a compression audit.
  • Keep a history. Insert the result into a permanent table from a SQL Agent job and compare weeks. Tables that appear, disappear or move between databases show up as differences.
  • Include system databases when auditing. Remove the NOT IN list, or list the system databases of interest, when the review needs msdb or model.
  • Check columns after a match. Query INFORMATION_SCHEMA.COLUMNS in the matched database to compare definitions before merging duplicate tables.

Conclusion

Reading sys.databases and building three-part sys.tables queries in one dynamic batch gives a complete, sorted table inventory without a cursor. The search variant and the version-gated extensions make it a standing tool for migrations, audits and clean-ups.

References

Posts in this series