Associate Database Name with Table List

Question

It is no problem to list all tables with schemas on a server

SELECT SCHEMA_NAME(schema_id), name FROM sys.tables

How can I determine which database the tables reside in ?

Answer 1

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