Wednesday, April 18, 2012

How to list Stored procedure parameters ?

One of the simple way to use sp_Help system stored procedure to get all parameters properties for a particular stored procedure but what if we have to list all stored procedures parameters using TSQL .
Here is a simple query :

Select OBJECT_NAME(SP.OBJECT_ID) AS ProcedureName,
PR.name AS Parameters, TY.name AS DataType, TY.max_length AS Length, TY.precision AS Precision
From sys.procedures SP
Inner Join sys.parameters PR On SP.object_id = PR.object_id
Inner Join sys.types TY On PR.user_type_id= TY.user_type_id
----Where OBJECT_NAME(SP.OBJECT_ID)  = ?


Cheer...