How to query list of all objects and their name from all db

Question

Schema name, DB Name, Object Type, Object name etc..

Answer 1

Hi @C, Manoharan (Cognizant)

Try this:

EXEC sp_MSforeachdb '
USE [?];
SELECT
    DB_NAME() AS DatabaseName,
    s.name AS SchemaName,
    o.type_desc AS ObjectType,
    o.name AS ObjectName
FROM
    sys.objects o
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
ORDER BY
    SchemaName,
    ObjectType,
    ObjectName;
';

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

from all db

There is no build-in function to get the informations from all databases, you have to query then from each database, e.g. using a cursor or a UNION ALL query, like

SELECT * FROM Database01.sys.objects 
UNION ALL 
SELECT * FROM Database02.sys.objects
UNION ALL ...

Answer 3

SELECT

s.name AS SchemaName,

o.name AS ObjectName,

o.type_desc AS ObjectType,

ps.used_page_count * 8 / 1024 AS SizeMB

FROM sys.objects o

JOIN sys.schemas s ON o.schema_id = s.schema_id

LEFT JOIN sys.dm_db_partition_stats ps ON o.object_id = ps.object_id

WHERE o.is_ms_shipped = 0 -- Excludes system objects

GROUP BY s.name, o.name, o.type_desc, ps.used_page_count

ORDER BY SchemaName, ObjectType, SizeMB DESC;