Question: I am working on a DbCompre tool. Basically my requirement is that through C#, generate stored procedure script in web application. First of all is it possible?

In SQL Server we generate script easily through right click or with the help of command `sp_helptext <ProcedureName>`.

Here is my code:

```
public DataTable generateScripts()
{
      SqlCommand cmd = new SqlCommand("sys.sp_helptext dloc_GetTasksRewarehousePutaway", AppCon);
      cmd.CommandType = CommandType.StoredProcedure;
      SqlDataAdapter adp = new SqlDataAdapter(cmd);
      adp.Fill(DS);
      adp.SelectCommand = cmd;
      return DS.Tables[0];
}

```

When code execute I am getting this error: 

> 
> Could not find stored procedure 'sys.sp\_helptext dloc\_GetTasksRewarehousePutaway'
> 
> 
> 

But in database this procedure is available.

Answer: 
Absolutely its possible, you need to pass the SP name as a parameter though to the parameter `objname`:-

```
SqlCommand cmd= new SqlCommand("sys.sp_helptext", AppCon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@objname", "dloc_GetTasksRewarehousePutaway");

```
Whole example to get stored procedure T-SQL text from database

```
// add connection string 
var conString = "Db Connection String";

// add SP name
var spName = "DbSpName";
var sb = new StringBuilder();

try
{
    using (var conn = new SqlConnection(conString))
    {
        DataSet ds = new DataSet();

        SqlCommand cmd = new SqlCommand("sys.sp_helptext", conn);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@objname",spName );

        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        sda.Fill(ds);
        sda.SelectCommand = cmd;

        var dt = ds.Tables[0];

        foreach (DataRow dtRow in dt.Rows)
        {
            sb.Append(dtRow["Text"].ToString());
        }
    }
}
catch (Exception ex)
{
    // log if something went wrong ex.Message
}

```