List of objects summary belong to user_ defined

Question

Hi I am looking to get a summary of all objects( tables,view,sequence,store proceduces ,functons) total objects list with in the database exculding own by the system objects. Secondly what objects are invalid as well. my following SQL comes with all object including system objects which not expected.
exmple there are user objects created with some schemas ex: dbo, CRMADM,CVADM,CVRED so all are user define . how we i get it . If can get it screma break down would would be great

use< database>
go
SELECT o.type,o.type_desc,count(*)
FROM sys.all_objects o
INNER JOIN sys.schemas s on o.schema_id = s.schema_id
LEFT OUTER JOIN sys.database_principals po ON o.principal_id = po.principal_id
LEFT OUTER JOIN sys.database_principals ps ON s.principal_id = ps.principal_id
where o.type IN ('P', 'V', 'FN', 'TR', 'IF', 'TF', 'U','SO','PK')
group by o.type,o.type_desc
GO

Answer 1

Hi @Ashwan ,

Please try this:

SELECT type,type_desc,count(*)  
FROM sys.all_objects  
where type IN ('P', 'V', 'FN', 'TR', 'IF', 'TF', 'U','SO','PK')  
 and is_ms_shipped = 0  
group by type,type_desc  

This will provide a list of all user-defined objects (is_ms_shipped=0). In addition, please refer to sys.all_objects which might help.

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.