SQL: Generate a list of tables for all databases on a server

The following Transact-SQL 1 can be used to generate a list of tables for all the databases on a server:

USE master
GO

DECLARE @dbID INT
DECLARE @name VARCHAR(500)
DECLARE @sql VARCHAR(1000)
 
CREATE TABLE #databasetables
( DatabaseID INT,
  DatabaseName VARCHAR(100),
  TableID INT,
  TableName VARCHAR(500),
  DateCreated DATETIME
)
 
DECLARE dbCursor CURSOR FAST_FORWARD 
FOR
      SELECT dbid, name
      FROM Master.dbo.sysdatabases
      WHERE name NOT IN ('master', 'msdb', 'tempdb', 'model')
 
OPEN dbCursor
FETCH NEXT FROM dbCursor INTO @dbID, @name
WHILE @@FETCH_STATUS = 0
BEGIN
      SET @sql = 'INSERT INTO #databasetables (DatabaseID, DatabaseName, TableID, TableName, DateCreated) '
      SET @sql = @sql + 'SELECT ' + str(@dbid) + ' AS dbid, ''' + @name + ''', id, name, crdate '
      SET @sql = @sql + 'FROM [' + @name + '].dbo.sysobjects '
      SET @sql = @sql + 'WHERE type = ''U'' '
      EXEC (@sql)
      FETCH NEXT FROM dbCursor INTO @dbID, @name
END
CLOSE dbCursor
DEALLOCATE dbCursor

SELECT * 
FROM #databasetables
 
DROP TABLE #databasetables
  1. Please ensure you test any script taken from my website on a test/development machine, before running on a production server.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.