How to get SQL database objects

SELECT name AS ObjName

       ,crdate AS CreatedDate

       ,refdate AS RefreshDate

       ,CASE [TYPE]

             WHEN 'AF' THEN 'Aggregate Function (CLR)'

             WHEN 'C' THEN 'CHECK Constraint'

             WHEN 'D' THEN 'Default or DEFAULT Constraint'

             WHEN 'F' THEN 'FOREIGN KEY Constraint'

             WHEN 'FN' THEN 'Scalar Function'

             WHEN 'FS' THEN 'Assembly (CLR) Scalar-function'

             WHEN 'FT' THEN 'Assembly (CLR) Table-valued Function'

             WHEN 'IT' THEN 'Internal Table'

             WHEN 'K' THEN 'PRIMARY KEY or UNIQUE Constraint'

             WHEN 'L' THEN 'Log'

             WHEN 'P' THEN 'Stored Procedure'

             WHEN 'PC' THEN 'Assembly (CLR) Stored-procedure'

             WHEN 'R' THEN 'Rule'

             WHEN 'RF' THEN 'Replication Filter Stored Procedure'

             WHEN 'S' THEN 'System Table'

             WHEN 'SN' THEN 'Synonym'

             WHEN 'SQ' THEN 'Service Queue'

             WHEN 'TA' THEN 'Assembly (CLR) DML Trigger'

             WHEN 'TF' THEN 'Table Function'

             WHEN 'TR' THEN 'SQL DML Trigger'

             WHEN 'TT' THEN 'Table Type'

             WHEN 'U' THEN 'User Table'

             WHEN 'V' THEN 'View'

             WHEN 'X' THEN 'Extended Stored Procedure'

       END AS ObjType

FROM SYSOBJECTS

ORDER BY [name]

Results: