CODEWITHSUNDEEP
sql-server
Niya
Niya

Reputation:

How to get database and tablenames along

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

Upvotes: 0

Views: 430

Answers (2)

Hapkido
Hapkido

Reputation: 1441

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

Upvotes: 0

Timothy Khouri
Timothy Khouri

Reputation: 31865

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

Upvotes: 1

Related Questions