Database Tables

Today I was asked for "a list of all tables in all databases" on a particular instance of SQL Server.  Knowing what was actually required was all "User" tables in all "Accessible" "User" databases I wrote the script below . It will return the ServerName, DatabaseName, SchemaName and TableName of all the user tables in all online read-writeable non system databases.

Hopefully someone will find this useful ;)

/*

      -----------------------------------------------------------------

      Get all user tables for all online, read-writable user databases

      -----------------------------------------------------------------

    

      For more SQL resources, check out SQLServer365.blogspot.co.uk

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

     

      -----------------------------------------------------------------

*/

-- Declare variables

DECLARE @MinDBID INT

DECLARE @MaxDBID INT

DECLARE @DatabaseName VARCHAR(100)

DECLARE @SQL VARCHAR(2000)

-- Check for temporary tables and drop them if they exists

IF OBJECT_ID('tempDB.dbo.#Database') IS NOT NULL

    DROP TABLE [#Database] ;

IF OBJECT_ID('tempDB.dbo.#Table') IS NOT NULL

    DROP TABLE [#Table] ;

   

-- Create temporary tables

CREATE TABLE #Database

    (

      ID INT IDENTITY(1, 1),

      DatabaseName VARCHAR(100)

    );

CREATE TABLE #Table

    (

      ID INT IDENTITY(1, 1),

      ServerName VARCHAR(100),

      DatabaseName VARCHAR(255),

      SchemaName VARCHAR(255),

      TableName VARCHAR(255)

    );

   

-- Get online read/writeable user databases

INSERT  INTO #Database ( DatabaseName )

SELECT  name

FROM    sys.databases

WHERE name NOT IN ( 'master', 'model', 'msdb', 'tempdb', 'distribution', 'ReportServer', 'ReportServerTempDB' )

AND DATABASEPROPERTYEX(name, 'Updateability') = 'READ_WRITE'

AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE' ;

--Set variables loop            

SELECT  @MinDBID = MIN(ID),

        @MaxDBID = MAX(ID)

FROM    #Database

-- Begin loop

WHILE @MinDBID <= @MaxDBID

    BEGIN

            -- Get DatabaseName

        SELECT  @DatabaseName = DatabaseName

        FROM    #Database

        WHERE   ID = @MinDBID

           

            -- Set SQL to run for each database

            SET @SQL =  ' USE ' + @DatabaseName + ';' + '

                              INSERT INTO #Table

                              SELECT

                                    @@ServerName AS ServerName,

                                    DB_NAME() AS DatabaseName,

                                    s.name AS SchemaName,

                                    t.name AS TableName

                              FROM

                                    sys.tables AS t

                                    INNER JOIN sys.schemas as s on s.[schema_id] = t.[schema_id]

                              WHERE

                                    [type] = ''U''

                              AND is_ms_shipped = 0' + ';'

            -- Try catch block to execute SQL and handle errors              

            BEGIN TRY

                  -- Get table information

                  EXEC ( @SQL

                        )

            END TRY

            BEGIN CATCH

                  SELECT  @DatabaseName,

                              message_id,

                              severity,

                              [text],

                              @SQL

                  FROM    sys.messages

                  WHERE   message_id = @@ERROR

                              AND language_id = 1033 -- British English

            END CATCH

      -- Get the next database

    SET @MinDBID = @MinDBID + 1

-- End loop

END

-- Return results

SELECT      ID,

            ServerName,

            DatabaseName,

            SchemaName,

            TableName

FROM  #Table

ORDER BY DatabaseName, SchemaName, TableName ASC;

GO

Enjoy

Chris