-- List All Catalogs

DECLARE @AllCatalogs table (catalog sysname) 
INSERT INTO @AllCatalogs (catalog) 
EXEC sp_msforeachdb 'SELECT DISTINCT TABLE_CATALOG FROM [?].information_schema.TABLES'
SELECT '<All>' AS catalog
UNION ALL
SELECT DISTINCT catalog FROM @AllCatalogs
WHERE NOT catalog IN ('master', 'model', 'msdb', 'tempdb')
ORDER BY catalog

-- List All Tables (with row count)

DECLARE @AllTables table (catalog sysname, [schema] sysname, name sysname, type sysname, rownum int) 
INSERT INTO @AllTables (catalog, [schema], name, type, rownum) 
EXEC sp_msforeachdb 'SELECT T.TABLE_CATALOG, T.TABLE_SCHEMA, T.TABLE_NAME, T.TABLE_TYPE, I.rows AS ROW_NUM FROM [?].information_schema.TABLES T LEFT OUTER JOIN [?]..sysindexes I ON (I.id = OBJECT_ID(T.TABLE_CATALOG + ''.'' + T.TABLE_SCHEMA + ''.'' + T.TABLE_NAME) AND I.indid < 2)'
SELECT catalog AS "Database", [schema] AS "Schema", name AS "Table Name", type AS "Type", rownum AS "Row Count" FROM @AllTables
WHERE NOT catalog IN ('master', 'model', 'msdb', 'tempdb')
ORDER BY catalog, [schema], name

-- List All Tables

DECLARE @AllTables table (catalog sysname, [schema] sysname, name sysname, type sysname) 
INSERT INTO @AllTables (catalog, [schema], name, type) 
EXEC sp_msforeachdb 'SELECT T.TABLE_CATALOG, T.TABLE_SCHEMA, T.TABLE_NAME, T.TABLE_TYPE FROM [?].information_schema.TABLES T'
SELECT catalog AS "Database", [schema] AS "Schema", name AS "Table Name", type AS "Type" FROM @AllTables
WHERE NOT catalog IN ('master', 'model', 'msdb', 'tempdb')
ORDER BY catalog, [schema], name

-- List All Stored Procedures

DECLARE @AllRoutines table (catalog sysname, [schema] sysname, name sysname, type sysname, definition text, datatype varchar(100), created datetime, altered datetime) 
INSERT INTO @AllRoutines (catalog, [schema], name, type, definition, datatype, created, altered) 
EXEC sp_msforeachdb 'SELECT ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION, DATA_TYPE = CASE WHEN DATA_TYPE LIKE ''%char%'' THEN DATA_TYPE + ''('' + LTRIM(RTRIM(STR(CHARACTER_MAXIMUM_LENGTH))) + '')'' WHEN DATA_TYPE IN (''decimal'', ''numeric'') THEN DATA_TYPE + ''('' + LTRIM(RTRIM(STR(NUMERIC_PRECISION))) + '','' + LTRIM(RTRIM(STR(NUMERIC_SCALE))) + '')'' ELSE DATA_TYPE END, CREATED, LAST_ALTERED FROM [?].information_schema.ROUTINES'
SELECT catalog AS "Database", [schema] AS "Schema", name AS "Routine Name", type AS "Type", definition, datatype AS "Data Type", created AS "Created", altered AS "Altered" FROM @AllRoutines
WHERE NOT catalog IN ('master', 'model', 'msdb', 'tempdb')
ORDER BY catalog, [schema], name