Oh no! A useful cursor?


DECLARE @DB_NAME sysname;
SET @DB_NAME = NULL;

CREATE TABLE #RESULTS
(TABLE_CATALOG sysname,
TABLE_SCHEMA sysname)

DECLARE @SQL VARCHAR(2000);

DECLARE Cursor_DB CURSOR FAST_FORWARD READ_ONLY FOR

SELECT name FROM sys.databases
WHERE database_id > 4

OPEN Cursor_DB

FETCH NEXT FROM Cursor_DB INTO @DB_NAME
WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQL = 'USE ['+ @DB_NAME + '];
INSERT INTO #RESULTS
( TABLE_CATALOG, TABLE_SCHEMA )
SELECT DISTINCT TABLE_CATALOG,TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES
UNION ALL
SELECT DISTINCT TABLE_CATALOG,TABLE_SCHEMA FROM INFORMATION_SCHEMA.VIEWS
UNION ALL
SELECT DISTINCT ROUTINE_CATALOG,ROUTINE_SCHEMA FROM INFORMATION_SCHEMA.ROUTINES;'

PRINT @SQL;

EXEC (@SQL);

FETCH NEXT FROM Cursor_DB INTO @DB_NAME

END

CLOSE Cursor_DB
DEALLOCATE Cursor_DB

SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA FROM #RESULTS
WHERE TABLE_SCHEMA <> 'DBO'