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

Question

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 . . .

Percival

If you are concerned with entering the results in a source control system, it seems to me that the only real constraint is that it has to be the same each time it is generated with the same sprocs. Why the desire to keep things looking like the UI-generated output? – Uncounted

Good question. I have a whole bunch of sps which were originally acquired by saving from the gui. I'll just have to modify them. I thought that if there were some hook into getting the same output as the gui, why not go with that, but it looks like sp_helptext or smo (cur. mthd) are it. Thanks! – Percival

You're welcome and thank you for selecting my answer as the answer. BTW: I've had the same experience here on SO - I ask a question and end up with no answer but, instead, a resolution to take a different tack. – Uncounted

Oh yea - and do check out the MS Database Publishing Wizard. It is especially helpful if you have some "core" information in your DB that you also want to save! – Uncounted

Answer 1

EXEC sp_helptext 'your procedure name';

This avoids the problem with INFORMATION_SCHEMA approach wherein the stored procedure gets cut off if it is too long.

Update: David writes that this isn't identical to his sproc...perhaps because it returns the lines as 'records' to preserve formatting? If you want to see the results in a more 'natural' format, you can use Ctrl-T first (output as text) and it should print it out exactly as you've entered it. If you are doing this in code, it is trivial to do a foreach to put together your results in exactly the same way.

Update 2: This will provide the source with a "CREATE PROCEDURE" rather than an "ALTER PROCEDURE" but I know of no way to make it use "ALTER" instead. Kind of a trivial thing, though, isn't it?

Update 3: See the comments for some more insight on how to maintain your SQL DDL (database structure) in a source control system. That is really the key to this question.

Uncounted

Helpful, but not the identical source. – Percival

True, fairly trivial. I'm trying to come up with a simple way to put SQL Server Express procs into external source code control and compare current procs (in the db) against that source code control--which is why it wd be nice to always get exactly the same source as the GUI generates via a modify. – Percival

Actually, a stored proc to generate all of this automatically would be pretty trivial. Cursor through sys.objects where type = 'P' to get all the sprocs, submit each to sp_HelpText and then cursor through the results with ALTER swapped out. Generate output w/ Print, save to file. Submit to SVN – Uncounted

This would give you the same output every time so that it could be used in a source control system (like SVN). The other thing you could do is use the Microsoft SQL Server database publishing wizard (google it) to generate the entire structure of your database and submit it to SVN. – Uncounted

Answer 2

You will have to hand code it, SQL Profiler reveals the following.

SMSE executes quite a long string of queries when it generates the statement.

The following query (or something along its lines) is used to extract the text:

SELECT
NULL AS [Text],
ISNULL(smsp.definition, ssmsp.definition) AS [Definition]
FROM
sys.all_objects AS sp
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
WHERE
(sp.type = N'P' OR sp.type = N'RF' OR sp.type='PC')and(sp.name=N'#test___________________________________________________________________________________________________________________00003EE1' and SCHEMA_NAME(sp.schema_id)=N'dbo')

It returns the pure CREATE which is then substituted with ALTER in code somewhere.

The SET ANSI NULL stuff and the GO statements and dates are all prepended to this.

Go with sp_helptext, its simpler ...

Curdle

Answer 3

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;

Delightful

Answer 4

Use the following select statement to get the whole definition:

select ROUTINE_DEFINITION 
  from INFORMATION_SCHEMA.ROUTINES 
 where ROUTINE_NAME = 'someprocname'

I guess that SSMS and other tools read this out and make changes where necessary, such as changing CREATE to ALTER. As far as I know, SQL stores not other representations of the procedure.

Barrister

That certainly helps to retrieve the main stored procedure body (although one might just as well use sp_helptext). However it's not identical to what the GUI produces when I right-click on the procedure and select modify. Thanks, though. – Percival

DWright, Can you post a small sample of what changes it makes? Like can you make up a proc that hits the Northwind sample db. Then post the two sources. – Brattice

2DWright: actually, it's the GUI that's flawed and doesn't show the exact source (e.g. line numbers are screwed). – Delorenzo

watch out, this is capped at 4000 characters... – Rancell

Answer 5

I agree with Mark. I set the output to text mode and then sp_HelpText 'sproc'. I have this binded to Crtl-F1 to make it easy.

Whitelivered

@Whitelivered how do you bind it to ctrl-f1?? – Shahjahanpur

Answer 6

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

Manthei

Answer 7

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

Theran

The danger is that if a subsequent create fails, you've lost the procedure. – Crudity

Answer 8

I saw a article via link. There are four methods, I just did a short summary here for helping other programmers.

1. EXEC sp_helptext 'sp_name';

2. SELECT OBJECT_ID('sp_name')

3. SELECT OBJECT_DEFINITION( OBJECT_ID('sp_name') ) AS [Definition];

4. SELECT * FROM sys.sql_modules WHERE object_id = object_id('sp_name');

Coopery

Answer 9

To alter a stored procedure, here's the C# code:

SqlConnection con = new SqlConnection("your connection string");
con.Open();
cmd.CommandType = System.Data.CommandType.Text;
string sql = File.ReadAllText(YUOR_SP_SCRIPT_FILENAME);
cmd.CommandText = sql;   
cmd.Connection = con;
cmd.ExecuteNonQuery();
con.Close();

Things to note:

1. Make sure the USER in the connection string have the right to alter SP
2. Remove all the GO,SET ANSI_NULLS XX,SET QUOTED_IDENTIFIER statements from the script file. (If you don't, the SqlCommand will throw an error).

Gromwell