title: How can I retrieve SQL Stored Procedure Parameter's with SMO more efficently?
body: <p>I'm attempting to retrieve the DefaultValue of all the parameters in a StoredProcedure.  My application is build in C# .NET accessing a Microsoft SQL 2008 Database.</p>
  
  <p>I use the SqlCommandBuilder.DeriveParameters to get most of the parameter information rather efficiently however it does not return the "DefaultValue" of a parameter so I've resorted to SMO to get that particular property.</p>
  
  <p>Here's my current code :</p>
  
  <pre><code>        Server svr = new Server(new ServerConnection(new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString)));
          StoredProcedure sp = svr.Databases["MyDatabase"].StoredProcedures["mySproc", "myScheme"];
          svr.SetDefaultInitFields(typeof(StoredProcedureParameter), "Name");
          svr.SetDefaultInitFields(typeof(StoredProcedureParameter), "DefaultValue");
  
          Dictionary&lt;string, string&gt; defaultValueLookup = new Dictionary&lt;string, string&gt;();
          foreach (StoredProcedureParameter parameter in sp.Parameters)
          {
              string defaultValue = parameter.DefaultValue;
              string parameterName = parameter.Name;
              defaultValueLookup.Add(parameterName, defaultValue);
          }
  </code></pre>
  
  <p>However, this is very slow even after I added the svr.SetDefaultInitFields optimization (which did make a significant improvement ~10x improvement).</p>
  
  <p>Anybody got further optimization ideas?</p>