April 1, 2006 at 12:23 am
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
April 3, 2006 at 10:28 am
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]
April 3, 2006 at 10:36 am
...also...shouldn't have to say...but I will add anyway
Be ~PAINFULLY~ carefull when working with any of the system objects !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
April 4, 2006 at 4:14 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply