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:



image





I am a happy man now :)

at

5 comments:

  1. AnonymousJune 9, 2009 at 5:27 AM

    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

    ReplyDelete
  2. AnonymousDecember 21, 2009 at 4:35 PM

    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

    ReplyDelete
  3. UnknownFebruary 3, 2010 at 10:49 PM

    The second result set will also give the same

    EXEC sp_help 'procedure name'

    ReplyDelete
  4. DamisFebruary 17, 2010 at 10:16 AM

    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!

    ReplyDelete
  5. Suprotim AgarwalFebruary 17, 2010 at 11:21 AM

    Thanks Dimitris!

    ReplyDelete