How to Retrieve names of stored procedures in SQL Server?

Question

Hi, SO crowd!

Is there any way to retrieve the names of the stored procedures by some SELECT from the system table or any system SP?

I need to retrieve all the stored procedure names with their signatures (if possible) or just names. I know how to do that in MySql, but similar queries don't work (of course, since all the system DBs named differently).

Thank you!

Answer 1

This should do it:

SELECT  name
FROM    sys.objects
WHERE   type = 'P'

The ANSI way (which will work on both MySQL and MS SQL) is:

SELECT  ROUTINE_NAME
FROM    INFORMATION_SCHEMA.ROUTINES
WHERE   ROUTINE_TYPE = 'PROCEDURE'

Answer 2

Not sure if this would work in MySQL but it works in MS SQL.

exec sp_stored_procedures

Answer 3

select * from sysobjects where type=’p’