Alex Rivera | Logout

Programmatically retrieve SQL Server stored procedure source that is identical to the source returned by the SQL Server Management Studio gui?

Asked 2009-01-21T23:24:08.020
68

Any pointers on how I can programmatically get exactly the identical stored procedure source from SQL Server 2005, as when I right-click on that stored procedure in SQL Server Management Studio and select modify?

I'm trying using SMO, but there are some textual differences. The procedure always has CREATE, not ALTER, and there are some differences in the header, such as missing GOs in the version I'm getting programmatically. I can fix these up, but perhaps there is a better way?

Again, I'm in SQL Server 2005, using SMSE. Using SMO via Visual Studio 8 2008.

Update: Gotten some answers that tell the basics of how to retrieve the stored procedure. What I'm looking for is retrieving the text identical (or nearly identical) to what the GUI generates.

Example: for sp_mysp, right-click in Management Studio, select modify. This generates:

    USE [MY_DB]  
    GO  
    /****** Object:  StoredProcedure [dbo].[sp_mysp]    Script Date: 01/21/2009 17:43:18 ******/  
    SET ANSI_NULLS ON  
    GO  
    SET QUOTED_IDENTIFIER ON  
    GO  
    -- =============================================
    -- Author:      
    -- Create date: 
    -- Description: 
    -- =============================================
    ALTER PROCEDURE [dbo].[sp_mysp]

I'd like to programmatically get the same thing (notice the GOs in the header, and the fact that it's an ALTER PROCEDURE. Ideally, I'd like to get this with minimal programmatic fixing up of the source retrieved.

I'd be happy to only get something that differed in the Script Date details . . .

sql-server stored-procedures smo
Edit
Report

3 Answers

12

You said programmatically, right? I hope C# is ok. I know you said that you tried SMO and it didn't quite do what you wanted, so this probably won't be perfect for your request, but it will programmatically read out legit SQL statements that you could run to recreate the stored procedure. If it doesn't have the GO statements that you want, you can probably assume that each of the strings in the StringCollection could have a GO after it. You may not get that comment with the date and time in it, but in my similar sounding project (big-ass deployment tool that has to back up everything individually), this has done rather nicely. If you have a prior base that you wanted to work from, and you still have the original database to run this on, I'd consider tossing the initial effort and restandardizing on this output.

using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
…
string connectionString = … /* some connection string */;
ServerConnection sc = new ServerConnection(connectionString);
Server s = new Server(connection);
Database db = new Database(s, … /* database name */);
StoredProcedure sp = new StoredProcedure(db, … /* stored procedure name */);
StringCollection statements = sp.Script;
answered 2009-06-02T04:56:19.610
4

The Databse Publishing Wizard can dump the schema (and other objects) from the command line.

answered 2009-01-22T05:34:05.090
2

I just want to note that instead of using find and replace to change create procedure to alter procedure, you are just as well to use a drop, you can put it right at the top and it does require text searching. 

IF exists (SELECT * FROM sys.objects 
        WHERE object_id = OBJECT_ID(N'sp_name')
            and type in ('P','V') --procedure or view
        )
    DROP sp_name
GO

If you are sure it's there, I guess you could just drop it too, but I wouldn't recommend that. Don't forget the go, since create procedure must be the first and only statement in a batch.

Or the lazy approach:

IF OBJECT_ID(N'sp_name') is not null
    DROP sp_name
GO
answered 2012-02-03T13:34:24.037

Your Answer