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