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_dateshows when a table appeared, andmodify_dateshows the lastALTERof the table or the last creation or alteration of one of its indexes. - Tolerates unusual database names.
QUOTENAMEandREPLACEhandle spaces, brackets and quotes without per-name special cases. - Easy to extend. Extra columns from
sys.tables, or joins tosys.partitionsin 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_nameor addTOPwhen 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.
#AllTableslives intempdband 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_descreports 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_optimizedneeds SQL Server 2014 or later, andis_externalneeds SQL Server 2016 or later. Add them to the temporary table and theSELECTlist only on instances that have them, or the batch fails on older builds. - Count rows approximately.
sys.partitions.rowsis the approximate number of rows in a partition. Summing it forindex_idvalues 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_descreports 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 INlist, or list the system databases of interest, when the review needsmsdbormodel. - Check columns after a match. Query
INFORMATION_SCHEMA.COLUMNSin 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
- STRING_AGG (Transact-SQL) — return types by input type, ordering with WITHIN GROUP, SQL Server 2017 and later
- SELECT @local_variable (Transact-SQL) — why recursive variable assignment across rows is not guaranteed
- sys.databases (Transact-SQL) — state, state_desc, user_access_desc and the permissions needed to see a database row
- sys.tables (Transact-SQL) — inherited columns and version-specific columns such as is_memory_optimized and is_external
- sys.objects (Transact-SQL) — create_date, modify_date and is_ms_shipped
- sys.partitions (Transact-SQL) — rows, index_id and data_compression_desc
- sp_executesql (Transact-SQL) — Unicode statement requirement and database context behavior
- QUOTENAME (Transact-SQL) — delimiters and the 128-character input limit
- System databases — roles of master, model, msdb and tempdb
Posts in this series
- SQL Server Scripts and Commands: DBA Reference All Versions
- SQL Server DBCC CHECKIDENT: Bulk Identity Column Analysis
- SQL Server Error Log Search Script: xp_readerrorlog
- SQL Server Search Stored Procedure and View Text
- SQL Server Agent Job Failure History Report
- SQL Server Kill Sessions: Filtered SPID Management
- SQL Server MAXDOP Recommendation Script
- SQL Server Max Server Memory Calculator Script
- SQL Server Object Dependencies Report: Find All References
- SQL Server DBCC CHECKIDENT: Check and Reseed Identity
- List All Tables Across All Databases in SQL Server
- Configure Database Mail and Send a Test Message
- Audit Server Configuration Drift with sys.configurations
- Check AG Replica Health with sys.dm_hadr_availability_replica_states
- Audit Per-Database MAXDOP, CE and Sniffing Config Overrides