Create Combined DB Object Table

/*
sqryCreateCombinedDBObjectTable
Created by Larry Ansley 5/16/03.

This script forms a temporary table containing database and object names and IDs as well as object types for all databases for the server on which this scrip is executed.  It is intended to serve as an interim step for additional server wide analysis.
*/

Set NoCount On

Declare
  @DBName VarChar(50), @DBID Int

Create Table #Objects
  (DBID SmallInt, ObjID Int,
  ObjName VarChar(100), ObjType Char(5))

Set @DBID = 0

-- Combine the object info from all databases into a single temp table.
While @DBID <
  (Select Top 1 DBID From Master..sysdatabases
  Where DBID > @DBID Order By DBID)

  Begin

    Set @DBID =
    (Select Top 1 DBID From Master..sysdatabases
    Where DBID > @DBID Order By DBID)

    Set @DBName =
    (Select name From Master..sysdatabases
    Where DBID = @DBID)

    Execute
      ('Insert #Objects
      Select '   + @DBID +
      ' as DBID, o.id, Left(o.name,100) as ObjName,
      o.xtype as ObjType
      From ' + @DBName + '..sysobjects o')

  End

-- Display the combined list of objects.
Select #o.DBID, db.Name as dbName,
  #o.ObjType, #o.ObjID, #o.ObjName
From #Objects #o
Join Master..sysdatabases db
  on #o.DBID = db.DBID
Order By #o.DBID, #o.ObjType, #o.ObjID

Drop Table #Objects