Wednesday 13 June 2012

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