Find Out The Parameters Of A Stored Procedure in SQL Server 2005/2008

Recently while working on an application that heavily uses Stored Procedures, I had to often open up SQL Server Management Studio/ Visual Studio Server Explorer to physically check the parameters of a Stored Procedure. This became cumbersome at times since the procs were in different databases and I wish there were a query that returned me the Stored Procedure parameters, as soon as I supply it with the stored procedure name.

If you have been looking out for something similar, here’s what I came up with:

SELECT parm.name AS Parameter,        
typ.name AS [Type]
FROM sys.procedures sp
JOIN sys.parameters parm ON sp.object_id = parm.object_id
JOIN sys.types typ ON parm.system_type_id = typ.system_type_id
WHERE sp.name = 'aspnet_Membership_GetUsers'

OUTPUT:

I am a happy man now :)

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

5 comments:

Anonymous said...: great man

but you can use this query like

SELECT parm.name AS Parameter,
typ.name AS [Type]
FROM sys.objects sp
JOIN sys.parameters parm ON sp.object_id = parm.object_id
JOIN sys.types typ ON parm.system_type_id = typ.system_type_id
WHERE sp.name = 'aspnet_Membership_GetUsers'

so it will useful for both procedure and functions also
Best Regards,
Raj Acharya; June 9, 2009 at 5:27 AM
Anonymous said...: thanks mate. was looking for something like this and found this useful.

I also needed something that would work for sql2000 as well and came across the below query that works for 2000 and 2005.

SELECT PARAMETER_MODE, PARAMETER_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_NAME='PROC NAME'

cheers
Prem; December 21, 2009 at 4:35 PM
Unknown said...: The second result set will also give the same

EXEC sp_help 'procedure name'; February 3, 2010 at 10:49 PM
Damis said...: Nice Post,

But in case you have created your own data types that are i.e. datetime your query would return more than one rows.
In that case I run the following query

SELECT parm.name AS Parameter,
typ.name AS [Type]
FROM sys.procedures sp
JOIN sys.parameters parm ON sp.object_id = parm.object_id
JOIN sys.types typ ON parm.user_type_id = typ.user_type_id
WHERE sp.name = 'aspnet_Membership_GetUsers'

Thanks!; February 17, 2010 at 10:16 AM
Suprotim Agarwal said...: Thanks Dimitris!; February 17, 2010 at 11:21 AM