2

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?

7

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;
  • 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? – Hikari Sep 14 '15 at 14:21
  • 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. – Aaron Bertrand Sep 14 '15 at 14:22
  • 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. – Hikari Sep 14 '15 at 14:33
  • 1
    @Hikari That's great, but remember answers here are for all future readers, not just you. – Aaron Bertrand Sep 14 '15 at 14:34
3

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
  • Good point, I will incorporate that into my answer, thanks – Aaron Bertrand Sep 14 '15 at 14:11
2

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
0

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;
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.