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

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.

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