Find parameters in Stored procedures that are required

[tshad]

Is there a way to find out dynamically what parameters are in a stored
procedure and which are required?

Thanks,

Tom

[John Bell]

"tshad" <to...@pdsa.com> wrote in message
news:OgW3fq8C...@TK2MSFTNGP04.phx.gbl...

Hi Tom

I don't think there is as the has_default_value and default_value columns in
sys.parameters only relates to CLR procedures.

John

[tshad]

"John Bell" <jbellne...@hotmail.com> wrote in message
news:%23Za4vz8...@TK2MSFTNGP05.phx.gbl...

That is kind of a pain.

I would have thought there would be a default showing for the parameters
that have one.

Oh well.

Thanks,

Tom
>
> John

[Aaron Bertrand [SQL Server MVP]]

> I would have thought there would be a default showing for the parameters
> that have one.

Unfortunately the presence/absence of default parameters (never mind their
values!) is not a fact stored in metadata at this time. You can see
Default/No Default in Management Studio, but the application code actually
parses the text of the procedure to figure out that there are default values
in use. Gert Drapers once indicated he would share the code that does this
but then he moved to a different product (VSTS). I filed a bug about this
in 2006, and it is still active, meaning there is hope that with enough
votes they may fix it in a future version:

http://connect.microsoft.com/sql/feedback/ViewFeedback.aspx?FeedbackID=23414
3