Databases
StackOverflow2013
Postsdbo
POProgrammatically retrieve SQL Server stored procedure source that is identical to the source returned by the SQL Server Management Studio gui?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POProgrammatically retrieve SQL Server stored procedure source that is identical to the source returned by the SQL Server Management Studio gui?
    primarykey
    data
    text
    <p>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?</p> <p>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?</p> <p>Again, I'm in SQL Server 2005, using SMSE. Using SMO via Visual Studio 8 2008.</p> <p><b>Update</b>: 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. </p> <p>Example: for sp_mysp, right-click in Management Studio, select modify. This generates:</p> <pre> 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] </pre> <p>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.</p> <p>I'd be happy to only get something that differed in the Script Date details . . .</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMackM
    1. USDWright
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POProgrammatically retrieve SQL Server stored procedure source that is identical to the source returned by the SQL Server Management Studio gui?
      1. USCade Roux
      1. VTFavorite
    2. VO
      singulars
      1. POProgrammatically retrieve SQL Server stored procedure source that is identical to the source returned by the SQL Server Management Studio gui?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POProgrammatically retrieve SQL Server stored procedure source that is identical to the source returned by the SQL Server Management Studio gui?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIf 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?
      singulars
      1. POProgrammatically retrieve SQL Server stored procedure source that is identical to the source returned by the SQL Server Management Studio gui?
      1. USMark Brittingham
    2. COGood 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!
      singulars
      1. POProgrammatically retrieve SQL Server stored procedure source that is identical to the source returned by the SQL Server Management Studio gui?
      1. USDWright
    3. COYou'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.
      singulars
      1. POProgrammatically retrieve SQL Server stored procedure source that is identical to the source returned by the SQL Server Management Studio gui?
      1. USMark Brittingham
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload