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");

```
```
using (SqlConnection con = new SqlConnection ("Connection String Here"))
{
    using (SqlCommand cmd = con.CreateCommand())
    {
        cmd.CommandText = "sp_helptext @procName";//Stored Procedure name
        cmd.CommandType = CommandType.Text;

        cmd.Parameters.AddWithValue("procName", "dloc_GetTasksRewarehousePutaway");

        con.Open(); 

        using (SqlDataReader rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
            {
                /* 
                    You will get the CREATE PROC text here
                    Do what you need to with it. For example, write
                    to a .sql file
                */
            }
        }
    }
}

```