Here's a quick sample on using the SchemaExplorer API to get information about your stored procedures. You would do a similar task to try and create command wrapper methods or script out procedures drop and create DDL.


Name: CommandDumper.cst
Author: Robert Hinojosa
Description: Dumps the essentials of all of the stored procedures in a given database.
<%@ CodeTemplate Language="C#" TargetLanguage="Text" Src="" Inherits="" Debug="False" Description="Template description here." %>
<%@ Assembly Name="System.Data" %>
<%@ Import Namespace="System.Data" %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="System.Design" %>
<%@ Assembly Name="SchemaExplorer.SqlSchemaProvider" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="System.Collections" %>
<%@ Import Namespace="System.Text" %>

    // You can also just use a DatabaseSchema property to set all this stuff through the property grid.
    // I'm doing this to show how it can be done programatically
    string connectionString = "server=(local);database=Northwind;Integrated Security=true;";
    SchemaExplorer.SqlSchemaProvider provider = new SchemaExplorer.SqlSchemaProvider();
    DatabaseSchema db = new DatabaseSchema(provider, connectionString);
    Response.IndentLevel = 1;
    Response.WriteLine("Database Name:", db.Name);
    string currentCommand = "";
    string currentCommandProc = "";
    string exceptionMessage = "\n\n//TODO: I can't create sql objects when doing discovery, please skip me.{0}\n\nException:\n{1}\n\nProcedure: \n{2}";
    string generalException = "\n\n//TODO: An error occured. {0}\n\nException:\n{1}\n\nProcedure: \n{2}";
        //Loop through the Procedures
        foreach(CommandSchema proc in db.Commands)
                Response.IndentLevel = 2;
                currentCommand = proc.Name;
                currentCommandProc = proc.CommandText;
                //Loop through all input params, you would probably iterate through all of the output params as well.
                StringBuilder sb = new StringBuilder();
                for(int i=0;i<proc.AllInputParameters.Count;i++)
                    sb.AppendFormat("{0},", proc.AllInputParameters[ i ].Name);
                Response.WriteLine("- Procedure Name: {0} - Params: {1}", proc.Name, sb.ToString().TrimEnd(','));
                int idx = 0;
                //Since a procedure can have more than 1 result set, display them all
                foreach(CommandResultSchema result in proc.CommandResults)
                    Response.IndentLevel = 3;
                    Response.WriteLine("+ Returned Result Set #: {0}", idx);
                    //iterate through columns of result set and their type
                    foreach(CommandResultColumnSchema column in result.Columns)
                        Response.IndentLevel = 4;
                        Response.WriteLine("* Name: {0} - Type: {1}({2})", column.Name, column.NativeType, column.Size);
            catch(Exception exc)
                //Things such as temporary tables aren't good for discovery since codesmith isn't actually executing the proc, 
                //That object does not exist, and usually an exception is thrown.
                //Consider using in memory Table Variables instead.  
                Response.WriteLine(exceptionMessage, currentCommand, exc.ToString(), currentCommandProc);
    catch(Exception exc)
        Response.WriteLine(generalException, currentCommand, exc.ToString(), currentCommandProc);