How to find the parameters passed in stored procedures

  •  

    Hai,

      I need a help on how to retrieve what are all the input & output parameters passed in one procedure. i.e if i give one procedure name as an input then i need a query to return only the input & outparameters of the procedure. Also, is there any system tables stores all the parmeter values.

     

    Thanks,

    Manokarnan

  • The parameters for a stored proc are kept in the sys_columns table of a given database.

    The following query can be used to review the basic schema (user created stuff) for a given database. 

    You can use it to review column names for tables, and views... and it returns the parameters for stored procs. 

    There's even a bit column ("outparam") there to tell you if its an input or output parameter

    select  top 100 percent

     so.type,

     TypeDesc = Case so.type

       when 'U' then 'Table'

       when 'V' then 'View'

       when 'P' then 'StoredProc'

         end,

     so.[id],

     so.[name],

     colname = sc.[name],

     datatype = st.[name],

     length = sc.length,

     nullable = sc.isnullable,

     outparam = sc.isoutparam

     

    from dbo.sysobjects SO

    left outer join dbo.syscolumns SC

     on SO.[id] = SC.[id]

    inner join dbo.systypes ST

     on sc.xtype = st.xtype

    where  so.type in ('U', 'V', 'P')

     and so.status >= 0

    order by so.type,

     so.[id] 



    The ~BEST~ solution is always the simplest one!

  • ...also...shouldn't have to say...but I will add anyway

    Be ~PAINFULLY~ carefull when working with any of the system objects !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!



    The ~BEST~ solution is always the simplest one!

  • I just need to mention that TOP 100 PERCENT is completely unnecessary and is a bad habit to get into. Only use top if you want a portion of the records.

    Other thing, this will only work in SQL 2000. In SQL 2005 you would use sys.columns instead.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply