SQL Server: sp_ and xp_ procedures

Getting list of stored procedures from all_objects

select
-- sch.name schema_name, -- Always sys
   obj.name,
   obj.type -- P: Stored procedure | X: Extended stored procedure
from
   sys.all_objects obj    join
   sys.schemas     sch on obj.schema_id = sch.schema_id
where
   is_ms_shipped = 1 and
   type_desc in ('SQL_STORED_PROCEDURE' , 'EXTENDED_STORED_PROCEDURE')
order by
   obj.name