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

This is the SMO answer in PowerShell:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" "MyServer\MyInstance"
$db = $srv.Databases["MyDatabase"];
$proc = $db.StoredProcedures["MyStoredProcedure"]

foreach($parameter in $proc.Parameters) {
  if ($parameter.DefaultValue){
     Write-Host "$proc ,  $parameter , $($parameter.DefaultValue)"
  }
  else{
     Write-Host "$proc ,  $parameter , No Default Value"
  }
 }

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

Not a big deal in SQL Server 2005 and up:

SELECT 
    pa.NAME, 
    t.name 'Type',
    pa.max_length,
    pa.has_default_value,
    pa.default_value
FROM 
    sys.parameters pa
INNER JOIN 
    sys.procedures pr ON pa.object_id = pr.object_id
INNER JOIN 
    sys.types t ON pa.system_type_id = t.system_type_id
WHERE 
        pr.Name = 'YourStoredProcName'

Unfortunately, even though this seemed like a piece of cake - it doesn't work :-(

From Technet:

SQL Server only maintains default values for CLR objects in this catalog view; therefore, this column has a value of 0 for Transact-SQL objects. To view the default value of a parameter in a Transact-SQL object, query the definition column of the sys.sql_modules catalog view, or use the OBJECT_DEFINITION system function.

So all you can do is either query sys.sql_modules or call SELECT object_definition(object_id) to basically get the SQL definition (the T-SQL source code) for your stored proc and then you'd need to parse that (sucks!! big time.....)

Seems like there's really no other way to do this ... I'm amazed and appaled.....

Maybe in SQL Server 2008 R2 ? :-) Marc