Retrieving Stored Procedure Script And Parameters Separately

I want to access the stored procedure script using C#. I used EXEC sp_HelpText STOREDPROCNAME. This is working fine. Is there any way to retrieve stored procedure query and the

Solution 1:

The simplest way is to ask the db directly with SqlCommandBuilder.DeriveParameters

Retrieves parameter information from the stored procedure specified in the SqlCommand and populates the Parameters collection of the specified SqlCommand object.

There is also

exec [dbname].[sys].[sp_procedure_params_rowset] @procedure_name=N'uspblabla'

the source of which you can see via sp_helptext sp_procedure_params_rowset.

Solution 2:

You can get the body this way, but there isn't a way to parse out the individual statements:

SELECT definition FROM sys.sql_modules 
WHERE [object_id] = OBJECT_ID('dbo.procedurename');

You can get its parameters by:

SELECT name, system_type_id, max_length, precision, scale
FROM sys.parameters
WHERE [object_id] = OBJECT_ID('dbo.procedurename');

Note that you can't determine without brute force parsing of the definition whether the parameters have a default value, and if they do, what the default value is. You are better equipped to do these things in C# using RegEx or other parsing methods, or PowerShell like the ParamParser project I started here.

Solution 3:

You can add a reference to Microsoft.SqlServer.Smo and use code similar to the following:

voidMain()
{
    Serverserver=newServer("server");
    Databasedb= server.Databases["database"];

    stringsprocName="StoredProcName";

    StoredProcedureproc= db.StoredProcedures[sprocName];
    if (proc != null)
    {
        foreach (StoredProcedureParameter parameter in proc.Parameters)
        {
            Console.WriteLine("{0}:  {1}", parameter.DataType.Name, parameter.Name);
        }

        Console.WriteLine(proc.TextBody);
    }
}

Additional information can be found Here