How can I determine if a SQL Server stored procedure parameter has a default?

Question

Is there a way to determine programmatically if a SQL Server stored procedure parameter has a default? (Bonus points if you can determine what the default is.) SqlCommandBuilder.DeriveParameters() doesn't even try.

Thanks in advance for your help!

EDIT: I honestly don't care if it's a SQL Query, an SMO object, etc.

Answer 1

Run the builtin sp_help stored procedure?

Answer 2

I found a way using SMO:

Server srv; 
srv = new Server("ServerName"); 

Database db; 
db = srv.Databases["MyDatabase"]; 

var Params = db.StoredProcedures["MyStoredProc"].Parameters;

foreach(StoredProcedureParameter param in Params) {
    Console.WriteLine(param.Name + "-" + param.DefaultValue);
}

Answer 3

For stored procedures, I believe you would have to write something that parses T-SQL, or use the T-SQL parser that Microsoft provides.

The parser and script generator live in two assemblies. The Microsoft.Data.Schema.ScriptDom contains provider agnostic classes and the Microsoft.Data.Schema.ScriptDom.Sql assembly contain classes for the parser and script generator that are SQL Server specific.

How to specifically use this to identify the parameters and whether they're defaulted isn't covered and would be something you'd have to work on (probably with a deal of effort) using the sample code.

Answer 4

This is kind of a hack, but you could always just give optional parameters a special name like:

@AgeOptional = 15

...then write a simple method that checks a parameter to see if it's optional. Not ideal, but given the situation, it might actually be a decent solution.