Get the Count of User Defined Objects in SQL

SELECT 

    CASE TYPE 

        WHEN 'U' 

            THEN 'User Defined Tables' 

        WHEN 'S'

            THEN 'System Tables'

        WHEN 'IT'

            THEN 'Internal Tables'

        WHEN 'P'

            THEN 'Stored Procedures'

        WHEN 'PC'

            THEN 'CLR Stored Procedures'

        WHEN 'X'

            THEN 'Extended Stored Procedures'

    END, 

    COUNT(*)     

FROM SYS.OBJECTS

WHERE TYPE IN ('U', 'P', 'PC', 'S', 'IT', 'X')

GROUP BY TYPE