Get All Database and Tables in SQL

DECLARE @DBName VARCHAR(256)
DECLARE @varSQL VARCHAR(512)
DECLARE @getDBName CURSOR

SET @getDBName = CURSOR FOR

SELECT name
FROM sys.databases where name NOT IN (‘master’, ‘tempdb’, ‘model’, ‘msdb’)

CREATE TABLE #TmpTable (DBName VARCHAR(256),
SchemaName VARCHAR(256),
TableName VARCHAR(256))

OPEN @getDBName
FETCH NEXT
FROM @getDBName INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @varSQL = ‘USE ‘ + @DBName + ‘;
INSERT INTO #TmpTable
SELECT ”’+ @DBName + ”’ AS DBName,
SCHEMA_NAME(schema_id) AS SchemaName,
name AS TableName
FROM sys.tables’

EXEC (@varSQL)
FETCH NEXT
FROM @getDBName INTO @DBName
END

CLOSE @getDBName
DEALLOCATE @getDBName
SELECT * FROM #TmpTable

Leave a Reply

Your email address will not be published. Required fields are marked *