List all tables from all user databases 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?
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 &gt; 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;