Esta es la versión del script para SQL Server 2005+:

— obtener todos los db_id, object_id

 

SET NOCOUNT ON

 

GO

IF NOT OBJECT_ID (‘tempdb.dbo.##objetos’) IS NULL

  DROP TABLE ##objetos

 

CREATE TABLE ##objetos (

    database_id int, database_name sysname

  , object_id int, object_name sysname

  , index_id int, index_name sysname

  , type_desc nvarchar(60)

)

DECLARE c1 CURSOR READ_ONLY

FOR

  SELECT database_id, name FROM master.sys.databases

  WHERE database_id >= 5

 

DECLARE

  @database_id int

  , @db_name sysname

 

OPEN c1

FETCH NEXT FROM c1 INTO @database_id, @db_name

WHILE (@@fetch_status <> 1)

BEGIN

  IF (@@fetch_status <> 2)

  BEGIN

  DECLARE @sql NVARCHAR(4000)

  SET @sql = ‘INSERT INTO ##objetos (

      database_id, database_name,

      object_id, object_name,

      index_id, index_name, type_desc) ‘

  SET @sql = @sql + ‘SELECT ‘ +

      CAST(@database_id AS SYSNAME) + ‘, »’ + CAST(@db_name AS SYSNAME) + »’,

      o.object_id, o.name,

      i.index_id, i.name, i.type_desc

  FROM ‘ + QUOTENAME (@db_name) + ‘.sys.objects o JOIN ‘ +

  QUOTENAME (@db_name)

 

+ ‘.sys.indexes i ON o.object_id = i.object_id where

  o.type IN (»U», »V») AND i.index_id <> 0 ‘

 

  EXEC (@sql)

  END

  FETCH NEXT FROM c1 INTO @database_id, @db_name

END

CLOSE c1

DEALLOCATE c1

GO

 

SELECT * FROM ##objetos

 

 

 

Eladio Rincón