List all tables from all user databases

Question

sys.tables lists all user tables from a single database, But it doesn't have their schema name.

If I know existing databases when creating the code, I can use USE to browse all databases, query sys.tables for its tables, and insert the list into a temptable to use later.

But, is there an easier way to do it, and with a unique query be able to retrieve all user tables from all user databases with their schema?

Answer 1

I have a slightly different approach:

• I tend to caution against sp_msforeachdb because it is undocumented, unsupported, and has a known bug where it can silently skip databases, and Microsoft has no interest in fixing it (see here, here, here, here, and here).

• I like to build a dynamic SQL statement that can be inspected before execution, or printed out to be massaged first, instead of just throwing it into a black box. I also like to make sure the statement only attempts to run against databases that are online and that the user can access.

  DECLARE @src NVARCHAR(MAX), @sql NVARCHAR(MAX);
  SELECT @sql = N'', @src = N' UNION ALL 
  SELECT ''$d'', 
   s.name COLLATE SQL_Latin1_General_CP1_CI_AI,
   t.name COLLATE SQL_Latin1_General_CP1_CI_AI
   FROM [$d].sys.schemas AS s
   INNER JOIN [$d].sys.tables AS t
   ON s.[schema_id] = t.[schema_id]';
  SELECT @sql = @sql + REPLACE(@src, '$d', name)
   FROM sys.databases
   WHERE database_id > 4
   AND [state] = 0
   AND HAS_DBACCESS(name) = 1;
  SET @sql = STUFF(@sql, 1, 10, CHAR(13) + CHAR(10));
  PRINT @sql;
  --EXEC sys.sp_executesql @sql;
  

Note: this will bomb on databases with a single quote/apostrophe (') in the name. Which seems fair, because who does that?

If you need to put the results into a temp table for later use:

CREATE TABLE #t(d SYSNAME, s SYSNAME, t SYSNAME);
INSERT #t EXEC sys.sp_executesql @sql;

• Hikari: Very nice, thanks! This query adds [] to db names and not to schema and table. How can I make [] not be added to db names?
• Aaron Bertrand: The [ ] are needed in case you have database names with special characters or reserved words. I've changed it but it still may bomb if you have bad database naming conventions.
• Hikari: Yes I found it, SELECT @sql = @sql + REPLACE(@src, '$d', name). I don't needed it, we won't create db, schema or table with wacky names. And if somebody does it, it's better to find it quickly anyway.
• Aaron Bertrand: @Hikari That's great, but remember answers here are for all future readers, not just you.

Answer 2

Not enough reputation to comment but, here is a version of @AaronBertrand's answer if you have multiple database collations in a single instance. I had a vendor database using Latin1_General_BIN.

DECLARE @src NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @sql = N'', @src = N' UNION ALL 
SELECT ''$d'', s.name COLLATE SQL_Latin1_General_CP1_CI_AS, t.name COLLATE SQL_Latin1_General_CP1_CI_AS
 FROM $d.sys.schemas AS s
 INNER JOIN $d.sys.tables AS t
 ON s.[schema_id] = t.[schema_id]';
SELECT @sql = @sql + REPLACE(@src, '$d', QUOTENAME(name))
 FROM sys.databases
 WHERE database_id > 4
 AND [state] = 0
 AND HAS_DBACCESS(name) = 1;
SET @sql = STUFF(@sql, 1, 10, CHAR(13) + CHAR(10));
PRINT @sql;
--EXEC sys.sp_executesql @sql

• Aaron Bertrand: Good point, I will incorporate that into my answer, thanks

Answer 3

Below is one method, using the undocumented sp_MSforeachdb proc:

IF OBJECT_ID(N'tempdb..#TableList', 'U') IS NOT NULL
 DROP TABLE #TableList;
CREATE TABLE #TableList(
 DatabaseName sysname NOT NULL
 , SchemaName sysname NOT NULL
 , TableName sysname NOT NULL
 );
EXEC sp_MSforeachdb N'
IF N''?'' NOT IN(N''master'',N''model'',N''tempdb'',N''msdb'',N''SSISDB'')
BEGIN
 USE [?];
 INSERT INTO #TableList
 SELECT
 DB_NAME() AS DatabaseName
 , OBJECT_SCHEMA_NAME(object_id) AS SchemaName
 , name AS TableName
 FROM sys.tables;
END;';
SELECT
 DatabaseName
 , SchemaName
 , TableName
FROM #TableList
ORDER BY
 DatabaseName
 , SchemaName
 , TableName;
DROP TABLE #TableList;
GO

Answer 4

I took @Aaron Bertrand's answer and added the record count for each table in each database.

DECLARE @src NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @sql = N'', @src = N' UNION ALL 
SELECT ''$d'' as ''database'', 
 s.name COLLATE SQL_Latin1_General_CP1_CI_AI as ''schema'',
 t.name COLLATE SQL_Latin1_General_CP1_CI_AI as ''table'' ,
 ind.rows as record_count
 FROM [$d].sys.schemas AS s
 INNER JOIN [$d].sys.tables AS t ON s.[schema_id] = t.[schema_id]
 INNER JOIN [$d].sys.sysindexes AS ind ON t.[object_id] = ind.[id]
 where ind.indid < 2';
SELECT @sql = @sql + REPLACE(@src, '$d', name)
 FROM sys.databases
 WHERE database_id > 4
 AND [state] = 0
 AND HAS_DBACCESS(name) = 1;
SET @sql = STUFF(@sql, 1, 10, CHAR(13) + CHAR(10));
PRINT @sql;
--EXEC sys.sp_executesql @sql;

Answer 5

Pieced together from several other examples, and then cleaned up. It does used the 'undocumented' sp_MSforeachdb, but it is fast and works.

DECLARE @Tables TABLE
(
 ServerName NVARCHAR(128),
 DatabaseName NVARCHAR(128),
 SchemaName NVARCHAR(128),
 TableName NVARCHAR(128)
)
DECLARE @sql_text VARCHAR(500)
SET @sql_text = 'SELECT @@SERVERNAME 
 ,''?''
 ,s.name
 ,t.name
 FROM [?].sys.tables t 
 JOIN sys.schemas s on t.schema_id=s.schema_id 
 WHERE ''?'' NOT IN (''master'',''model'',''msdb'',''tempdb'')'
 -- Add -- in front of WHERE to include system databases 
INSERT INTO @Tables (ServerName, DatabaseName, SchemaName, TableName) EXEC sp_MSforeachdb @sql_text
SELECT *
FROM @Tables
ORDER BY DatabaseName, SchemaName, TableName