—
— 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
