SQL Server Performance Forum – Threads Archive
Is there a way to get a parameter list for a SP?
Is there an easy way to make a call to SQL Server 2005 to retrieve information about a stored procedure? Specifically, I’d like to obtain a list of the parameters that must be passed to a stored procedure. Can this be done easily? If you are a .NET developer, do you know of any functions within .NET that could help me gather this information? ThanksWhat about profiler?
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS†with no warranties for accuracy.
select * from syscolumns where id = object_id(‘your_sp’) KH
quote:Originally posted by khtan
select * from syscolumns where id = object_id(‘your_sp’)
select * from syscolumns where id = object_id(‘your_sp’)
Thanks! That’s perfect.
In SQL 2000 you could just use the object browser in Query Analyzer, open the branches under the sproc, then click-and-drag the Parameters folder into a query window. This will give you a comma-separated list of parameter names (you can obviously drop @RETURN_VALUE). Perhaps this might work – you’ll have to test it yourself: set up an ADO Connection, then set an ADO Command object, set the CommandText property to the sproc name, and loop through the Parameters collection of the Command object.
Check out the INFORMATION_SCHEMA.PARAMETER view in BOL. —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de
quote:Originally posted by rhartness
Thanks! That’s perfect.
quote:Originally posted by khtan
select * from syscolumns where id = object_id(‘your_sp’)
select * from syscolumns where id = object_id(‘your_sp’)
Thanks! That’s perfect.
Easy way, indeed. Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS†with no warranties for accuracy.
]]>