How to get database and tablenames along

Question

How to get all the database names and corresponding table names together ?

Answer 1

You will have to write a store procedure.

First get the database name

SELECT Name FROM master.sys.databases

For each database

SELECT %DatabaseName%, Name FROM %DatabaseName%.SysObjects WHERE type = 'U'

Edit here's the store procedure

CREATE PROCEDURE sp_GetDatabasesTables 
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
CREATE TABLE #schema ( DatabaseName VarChar(50), TableName VarChar(50) );
DECLARE @DatabaseName varchar(50);
DECLARE cursorDatabase CURSOR FOR
 SELECT Name FROM master.sys.databases WHERE Name NOT IN ('tempdb'); -- add any table you want to filter here

OPEN cursorDatabase;

-- Perform the first fetch.
FETCH NEXT FROM cursorDatabase INTO @DatabaseName;

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
 EXEC ('INSERT INTO #schema (DatabaseName, TableName) SELECT ''' + @DatabaseName + ''' AS DatabaseName, Name As TableName FROM ' + @DatabaseName + '.sys.SysObjects WHERE type = ''U'';');
 FETCH NEXT FROM cursorDatabase INTO @DatabaseName;
END

CLOSE cursorDatabase;
DEALLOCATE cursorDatabase;
SELECT * FROM #schema
END

Answer 2

CREATE TABLE #dbs ( DatabaseName VARCHAR(256), TableName VARCHAR(256) )

EXEC sp_msforeachdb 'INSERT INTO #dbs
    SELECT ''?'', [name] FROM dbo.SysObjects WHERE XType = ''U'''

SELECT * FROM #dbs
DROP TABLE #dbs