User Procedures

[jafisher2000]

Hi
Does anyone know how to return the list of user stored procedures in a database. The system stored procedure returns both system and user procedures. Alternatively is there a way to differentiate betweensystem and user procedures using the columns returned from sp_stored_procedures?
NUM_INPUT_PARAMS, NUM_OUTPUT_PARAMS and NUM_RESULT_SETS return -1. Also the PROCEDURE_NAME column has the proc name in the format &quot;<procName>;<1 or 0>&quot;. Is all this normal?

 

[sunila7]

Try this

SELECT * FROM INFORMATION_SCHEMA.ROUTINES Where ROUTINE_TYPE = 'PROCEDURE'

Hope it helps

Sunil

 

[jafisher2000]

The above method still returns system stored procedures. I want to get all procedures not created by the system

 

[JamesLean]

The only system SPs are ones which start with 'dt_' (they are used for database diagrams). So you can get the user ones with:

SELECT name
FROM sysobjects
WHERE type = 'p'
  AND name NOT LIKE 'dt_%'
ORDER BY name

--James

 

[JFoushee]

SELECT name
FROM sysobjects
WHERE (category & 2) <> 2

 

[JFoushee]

Whoops.

SELECT name
FROM sysobjects
WHERE (category & 2) <> 2 AND type = 'P'