List All Tables Across All Databases in SQL Server

calendar Jun 29, 2026 / · 11 min read · sql server sys.databases sys.tables sys.schemas dynamic sql cross database catalog views table inventory sp_executesql administration metadata scripts  ·
Share on: linkedin copy

A cross-database table inventory is one of the most common ad hoc requests in any SQL Server environment — and one of the few queries that cannot be answered by staying inside a single database context. sys.databases exposes every online database on the instance; three-part name references to sys.tables inside dynamic SQL pull the user-table list from each one. The script below accumulates results in a temp table and returns them sorted by database, schema, and table name, with a second variant that narrows results to a specific table name for cross-database search.

Purpose and Overview

Where sp_MSforeachdb can sweep every database in a single call, that procedure is undocumented, behaves inconsistently around databases with unusual names, and returns separate result sets that cannot be aggregated without additional workarounds. The dynamic SQL approach built on sys.databases and sys.tables delivers the same coverage with explicit control over which databases are included, a single sorted result set, and a clear query structure that extends naturally to additional catalog joins — row counts, index counts, compression state — without fighting undocumented behavior.

The pattern rests on two catalog objects. sys.databases is a server-scope view that returns one row per database registered on the instance, including the four system databases (master, model, msdb, tempdb) and every user database regardless of state. The state_desc column filters to ONLINE databases — databases in OFFLINE, RESTORING, or SUSPECT states are excluded because their catalog views are inaccessible and would produce errors during the dynamic batch. The database_id column carries the values 1 through 4 for the four system databases in a default install; filtering on database_id > 4 removes them without hard-coding names that might differ on named or Azure SQL Managed Instances.

sys.tables is a database-scope catalog view — it exists inside every database and returns one row per user table in that database context. Accessing it via a three-part name ([DatabaseName].sys.tables) in dynamic SQL allows the same query to run against any database without a USE statement, which is what makes the batch-building approach efficient: every INSERT in the concatenated @SQL string runs against a different database's system catalog in a single execution. The is_ms_shipped flag on sys.tables is set to 1 for tables created by Microsoft-supplied components — internal tables, queue tables, replication tracking objects — and filtering it to 0 restricts results to tables created by the application or DBA, which is the intent of a cross-database inventory.

Joining sys.tables to sys.schemas (also accessed via three-part name) resolves schema_id to a readable schema name. On most databases the default schema is dbo, but applications commonly use multiple schemas — Sales, HR, Staging — and including the schema in the output prevents ambiguity when two databases each contain a dbo.Customer table.

Code Breakdown

The script creates a temporary table in tempdb, builds a dynamic batch that iterates every online user database, executes the batch in a single call, then returns the collected results.

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

A second variant collects the full inventory first, then applies a name filter — the "find table in all databases" pattern:

 1-- ============================================================
 2-- Search for a table name across all online databases
 3-- ============================================================
 4
 5IF OBJECT_ID('tempdb..#AllTables') IS NOT NULL
 6    DROP TABLE #AllTables;
 7
 8CREATE TABLE #AllTables (
 9    database_name SYSNAME  NOT NULL,
10    schema_name   SYSNAME  NOT NULL,
11    table_name    SYSNAME  NOT NULL,
12    create_date   DATETIME NOT NULL
13);
14
15DECLARE @TableSearch SYSNAME       = N'Customer';  -- partial or exact name
16DECLARE @SQL         NVARCHAR(MAX) = N'';
17
18SELECT @SQL += N'
19INSERT INTO #AllTables (database_name, schema_name, table_name, create_date)
20SELECT
21    ' + QUOTENAME(name, '''') + N',
22    s.name,
23    t.name,
24    t.create_date
25FROM ' + QUOTENAME(name) + N'.sys.tables  AS t
26INNER JOIN ' + QUOTENAME(name) + N'.sys.schemas AS s
27    ON t.schema_id = s.schema_id
28WHERE t.is_ms_shipped = 0;'
29FROM sys.databases
30WHERE state_desc  = 'ONLINE'
31  AND database_id > 4;
32
33EXEC sp_executesql @SQL;
34
35-- Apply name filter after collection
36SELECT database_name, schema_name, table_name, create_date
37FROM #AllTables
38WHERE table_name LIKE N'%' + @TableSearch + N'%'
39ORDER BY database_name, schema_name, table_name;
40
41DROP TABLE #AllTables;

Building the dynamic SQL against sys.databases

The SELECT @SQL += ... pattern concatenates one INSERT INTO #AllTables ... SELECT ... statement per database row, building a single batch rather than looping through a cursor. QUOTENAME(name) wraps each database name in square brackets — [AdventureWorks], [My Database] — handling names that contain spaces, hyphens, or reserved keywords that would break a bare three-part reference. The second form, QUOTENAME(name, ''''), uses a single-quote delimiter instead of brackets, producing a string literal such as 'AdventureWorks' for use in the static database_name column of the SELECT list. SQL Server's QUOTENAME doubles any embedded single quotes in the name, so databases with apostrophes in their names are handled safely without additional escaping.

The state_desc = 'ONLINE' predicate excludes databases that SQL Server knows about but cannot currently serve queries against — databases in RESTORING state during a log-shipping apply window, OFFLINE databases taken out of service for maintenance, or SUSPECT databases that failed crash recovery. Attempting a three-part catalog reference against any of these would raise an error and abort the entire batch. The database_id > 4 filter removes the four built-in system databases by their fixed integer IDs: master (1), tempdb (2), model (3), and msdb (4). These IDs are invariant across all SQL Server installations.

Three-part name references to sys.tables and sys.schemas

The FROM [DatabaseName].sys.tables AS t syntax is a three-part name — catalog, schema, object. SQL Server's system catalog views are accessible this way without a USE statement, which is what allows the batch-building approach to work: each INSERT block runs against a different database's catalog within the same execution. This also avoids the sp_MSforeachdb dependency on an undocumented stored procedure and produces a single execution plan for the entire concatenated batch rather than one plan per database.

sys.schemas is joined on schema_id to translate the integer identifier to a readable name. The same three-part access pattern applies: [DatabaseName].sys.schemas. Both catalog views are server-accessible — they do not require the session's database context to be set to the target database, which is what makes the batch-building pattern safe to use from any connected database.

The is_ms_shipped filter

Every user database contains internal tables managed by SQL Server's own features — MSreplication_objects tables in replication-enabled databases, queue_messages_N tables for Service Broker queues, and various internal tracking tables created by Change Data Capture or other system features. These objects have is_ms_shipped = 1 in sys.tables. Filtering on is_ms_shipped = 0 restricts results to tables the application or DBA created. Remove this predicate only when the goal is an audit of internal system objects across databases rather than an application table inventory.

The table-name search variant

The search variant collects the full unfiltered inventory into #AllTables, then applies the LIKE predicate on the final SELECT rather than embedding it in the per-database dynamic SQL string. This keeps the dynamic batch simple — no additional string-escaping for the search pattern — and moves the filter to ordinary T-SQL where it is readable and easily adjusted. The trade-off is that all user tables across all databases are inserted before filtering; on an instance with hundreds of databases and thousands of tables each, embedding a WHERE t.name LIKE ... clause inside the per-database block reduces I/O at the cost of more complex string construction around the search term.

Key Benefits and Use Cases

  • Full instance inventory in one result set — all user tables across every online database, sorted for easy scanning, without issuing INFORMATION_SCHEMA.TABLES queries per-database manually or parsing sp_MSforeachdb output.
  • Cross-database table search — the search variant answers "does a table named X exist anywhere on this instance?" — the first question in a cross-database refactor, compliance audit, or migration assessment.
  • Extension-ready — the temp table accumulates only the columns needed; additional catalog joins inside the per-database SELECT block can add row count via sys.partitions, index count via sys.indexes, or compression state via sys.partitions.data_compression_desc without structural changes to the outer query.
  • Excludes system objects cleanly — is_ms_shipped = 0 removes internal and replication tables without requiring a hard-coded name exclusion list.
  • No undocumented procedure dependency — unlike sp_MSforeachdb, the script uses only documented catalog views and a documented system stored procedure, ensuring consistent behavior across SQL Server versions and editions.
  • Survives unusual database names — QUOTENAME handles database names containing spaces, hyphens, brackets, and reserved words that would break naive string concatenation.

Performance Considerations

  • Catalog-only query, minimal I/O: sys.tables and sys.schemas read in-memory catalog metadata — the script does not scan data pages and completes quickly even on instances hosting large databases. Execution time scales with database count, not database size.
  • Result set scales with object count: on an instance with 100 databases and 500 tables each, the final SELECT returns 50,000 rows. Add a WHERE database_name = N'TargetDB' predicate or a TOP clause to the final query when the full cross-database list is unnecessary.
  • Temp table is session-scoped: #AllTables is created in tempdb and visible only to the current session. The rows are narrow — five columns of metadata — so total tempdb consumption is negligible except on instances with extreme object counts.
  • Offline and inaccessible databases are silently excluded: the state_desc = 'ONLINE' filter means databases in recovery, being restored, or held in single-user mode by another session do not appear in results and generate no error. Add AND user_access_desc = 'MULTI_USER' to also exclude single-user-mode databases when that distinction matters.
  • Dynamic SQL executes as a single batch: the concatenated @SQL string is passed to sp_executesql once. On an instance with several hundred databases, verify the string does not approach the 2 GB NVARCHAR(MAX) limit — an edge case in practice, but worth noting in large fleet environments where each database contributes roughly 200–400 characters to the batch.

Practical Tips

  • Add t.type_desc to the per-database SELECT list to distinguish standard heap and clustered-index tables from memory-optimized tables; sys.tables.type_desc returns USER_TABLE for disk-based tables and MEMORY_OPTIMIZED_TABLE for In-Memory OLTP tables on editions that support them.
  • Extend the per-database block with a correlated subquery on sys.partitions to include approximate row count: (SELECT SUM(p.rows) FROM [DB].sys.partitions AS p WHERE p.object_id = t.object_id AND p.index_id < 2) — adding this gives a size-based triage view alongside the name inventory.
  • To include system databases in the output, remove AND database_id > 4 or name specific system databases of interest; msdb in particular carries many user-accessible tables such as backupset, sysjobs, and sysmail_mailitems that are worth cataloguing in an instance audit.
  • Schedule the script as a SQL Server Agent job that writes results to a permanent history table on a weekly basis — comparing the current week's output to the prior week surfaces newly created tables in production, dropped tables, and tables that migrated between databases without a formal deployment record.
  • After a cross-database search returns a match, follow up with SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'Customer' inside the matched database to compare column definitions — particularly useful when consolidating duplicate tables across multiple databases into a shared schema.

Conclusion

sys.databases crossed with three-part sys.tables references in dynamic SQL is the documented, reliable path to a complete cross-database table inventory on any SQL Server instance. The temp-table accumulation pattern keeps the per-database batch simple, collects results into a single sortable result set, and extends cleanly to include additional catalog attributes without structural changes. Paired with the table-name search variant, it answers both the "what tables exist everywhere" and the "where does this specific table live" questions that arise in every migration, compliance audit, and cross-database refactoring effort.

References

Posts in this series