Select user defined stored procedures
The following query returns user defined (non pre-installed) stored procedures:
select
sch.name schema_name,
alo.name object_name,
-- alo.parent_object_id,
alo.type object_type,
alo.type_desc object_type_desc,
alo.create_date create_dt,
alo.modify_date modif_dt,
alo.principal_id object_principal_id,
sch.principal_id schema_principal_id,
alo.is_ms_shipped,
alo.is_published,
alo.is_schema_published
from
sys.all_objects alo join
sys.schemas sch on alo.schema_id = sch.schema_id
where
is_ms_shipped <> 1 and
alo.type_desc = 'SQL_STORED_PROCEDURE';
types
The types that are found in sys.all_objects seem be:
select
type_desc,
type
-- count(*) cnt
from
sys.all_objects
group by
type_desc,
type
order by
type_desc;
--
-- type_desc type
-- ------------------------------------------------------------ ----
-- AGGREGATE_FUNCTION AF
-- CHECK_CONSTRAINT C
-- CLR_SCALAR_FUNCTION FS
-- CLR_STORED_PROCEDURE PC
-- CLR_TABLE_VALUED_FUNCTION FT
-- DEFAULT_CONSTRAINT D
-- EXTENDED_STORED_PROCEDURE X
-- FOREIGN_KEY_CONSTRAINT F
-- INTERNAL_TABLE IT
-- PRIMARY_KEY_CONSTRAINT PK
-- RULE R
-- SERVICE_QUEUE SQ
-- SQL_INLINE_TABLE_VALUED_FUNCTION IF
-- SQL_SCALAR_FUNCTION FN
-- SQL_STORED_PROCEDURE P
-- SQL_TABLE_VALUED_FUNCTION TF
-- SYNONYM SN
-- SYSTEM_TABLE S
-- TYPE_TABLE TT
-- UNIQUE_CONSTRAINT UQ
-- USER_TABLE U
-- VIEW V
Microsoft's documentation also lists PG (Plan Guide), however, I was not (yet?) able to find such a thing.
Definition of all_objects
sp_helptext 'sys.all_objects' returns
CREATE VIEW sys.all_objects AS
SELECT
o.name, o.id AS object_id, r.indepid AS principal_id,
o.nsid AS schema_id, o.pid AS parent_object_id,
convert(char(2), case o.type when 'ET' then 'U' else o.type end) AS type,
n.name AS type_desc,
o.created AS create_date, o.modified AS modify_date,
convert(bit, o.status & 1) AS is_ms_shipped, -- OBJALL_MSSHIPPED
convert(bit, o.status & 16) AS is_published, -- OBJALL_RPL_PUBLISHED
convert(bit, o.status & 64) AS is_schema_published -- OBJALL_RPL_PUB_SCHONLY
FROM sys.sysschobjs$ o
LEFT JOIN sys.syssingleobjrefs r ON r.depid = o.id AND r.class = 97 AND r.depsubid = 0 -- SRC_OBJOWNER
LEFT JOIN sys.syspalnames n ON n.class = 'OBTY' AND n.value = o.type
WHERE o.nsclass not in (20,21) -- excluding DDL trigger
AND HAS_ACCESS('AO', o.id) = 1