CODEWITHSUNDEEP
vb.netsql-server-2008-r2
Peter Sun
Peter Sun

Reputation: 1813

VB.NET grab stored procedure code

I am wondering if there is a way to grab the stored procedure code from a SQL Server 2008 R2?

For example, if I had the following code to create this stored procedure:

USE AdventureWorks;  
GO  
CREATE PROCEDURE HumanResources.uspGetEmployeesTest2   
    @LastName nvarchar(50),   
    @FirstName nvarchar(50)   
AS   

    SET NOCOUNT ON;  
    SELECT FirstName, LastName, Department  
    FROM HumanResources.vEmployeeDepartmentHistory  
    WHERE FirstName = @FirstName AND LastName = @LastName  
    AND EndDate IS NULL;  
GO

Is there a way I can write a SQL statement to get the text everything between 'SET NO COUNT ON' to 'IS NULL'?

Upvotes: 1

Views: 104

Answers (2)

marc_s
marc_s

Reputation: 754388

Pretty simple, really - just join the sys.procedures and sys.sql_modules system catalog views to get the stored procedure definition.

This C# code illustrates this approach:

string connectionString = "server=YourServerName;Database=YourDatabaseName;Integrated Security=SSPI;";

string query = @"SELECT m.definition 
                 FROM sys.procedures pr 
                 INNER JOIN sys.schemas s ON pr.schema_id = s.schema_id
                 INNER JOIN sys.sql_modules m ON pr.object_id = m.object_id 
                 WHERE pr.Name = @Name AND s.Name = @Schema;";

using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand(query, conn))
{
    cmd.Parameters.Add("@Name", SqlDbType.VarChar, 255).Value = "uspGetEmployeesTest2";
    cmd.Parameters.Add("@Schema", SqlDbType.VarChar, 255).Value = "HumanResources";

    conn.Open();
    object returnValue = cmd.ExecuteScalar();
    conn.Close();

    if (returnValue != null)
    {
        string procedureDefinition = returnValue.ToString();
    }
}

This returns you everything - from the CREATE PROCEDURE to the last line of your procedure

Upvotes: 1

Bozhidar Stoyneff
Bozhidar Stoyneff

Reputation: 3634

Sure. One way is to poke the INFORMATION_SCHEMA.ROUTINES. In a query editor you go like this:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE 
    ROUTINE_SCHEMA = 'HumanResources' 
    AND ROUTINE_NAME = `uspGetEmployeesTest2`

Then you could refine the text you need even further by playing with WHERE and LIKE.

Upvotes: 0

Related Questions