Associate Database Name with Table List

sys.tables exists in all databases so I am not following the fact that you don't know the db you are in. you can run DB_NAME(DB_ID()) to get the db name

SELECT  DB_NAME(DB_ID()),SCHEMA_NAME(schema_id), name FROM sys.tables

but in this case DB_NAME(DB_ID()) will return the same value for every row

to do it for all database, you can do this

EXEC sp_msforeachdb 'use  [?] SELECT  ''?'',SCHEMA_NAME(schema_id), name 
                                  FROM sys.tables'

You can of course dump it into a table as well

CREATE TABLE #output (DatabaseName VARCHAR(1000), 
                   SchemaName VARCHAR(1000), 
                  TableName VARCHAR(1000))

INSERT #output
EXEC sp_msforeachdb 'use  [?] SELECT  ''?'',SCHEMA_NAME(schema_id), name 
                                  FROM sys.tables'

SELECT * FROM #output

Just as a FYI, the sp_msforeachdb proc is undocumented and you should not use it for production code, to quickly find something is fine, for production code roll your own version of this proc

See also Aaron Bertrand's posts here:

• Making a more reliable and flexible sp_MSforeachdb
• Execute a Command in the Context of Each Database in SQL Server

I ran into this problem when I was creating query that I wanted to be able to run against a different database on my server, and include the other database's name name without hard-coding it into the query.

The query essentially looked like this:

SELECT DB_NAME() db_name
     , SCHEMA_NAME(schema_id) schema_name
     , name table_name
  FROM OtherDB.sys.tables   --The OtherDB is to specify that I am running
                            --this for a different database than the one
                            --I'm logged in to for my current session.

The problem was that even though I specify OtherDB.sys.tables in the from clause, DB_NAME() always returned the current database I was in. Yes, I could put a USE OtherDB at the beginning, but it seemed like there should be another way. I looked through every sys view I could find, but could never find anything that would link sys.databases and sys.tables.

What I eventually found was SQL Server's INFORMATION_SCHEMA.TABLES This view includes the Database name as the first column (referred to as TABLE_CATAOLG).

SELECT TABLE_CATALOG
     , TABLE_SCHEMA
     , TABLE_NAME
     , TABLE_TYPE
  FROM INFORMATION_SCHEMA.TABLES

With these views, you can easily compare the tables in two databases:

SELECT a.TABLE_CATALOG
     , a.TABLE_SCHEMA
     , a.TABLE_NAME
     , a.TABLE_TYPE
     , b.TABLE_CATALOG
     , b.TABLE_SCHEMA
     , b.TABLE_NAME
     , b.TABLE_TYPE
  FROM OneDatabase.INFORMATION_SCHEMA.TABLES a
  FULL OUTER JOIN TwoDatabase.INFORMATION_SCHEMA.TABLES b 
    ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
   AND a.TABLE_NAME = b.TABLE_NAME

If the databases are on separate servers that are linked you should be able to use this query by using all four parts of the Fully Qualified Table Name.

If your intention is just to include the current database name, why not just:

SELECT DB_NAME(), SCHEMA_NAME(schema_id), name FROM sys.tables;

If your intention is to pull all names from all databases, I personally prefer dynamic SQL like this instead of sp_msforeachdb:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += CHAR(13) + CHAR(10) + 'UNION ALL 
    SELECT ''' + name + ''', s.name, t.name
    FROM ' + QUOTENAME(name) + '.sys.tables AS t
    INNER JOIN ' + QUOTENAME(name) + '.sys.schemas AS s
    ON t.schema_id = s.schema_id'
    FROM sys.databases
    WHERE database_id > 4;

SET @sql = STUFF(@sql, 1, 13, '');

PRINT @sql;

-- EXEC sp_executesql @sql;