以编程方式检索与 SQL Server Management Studio gui 返回的源相同的 SQL Server 存储过程源? [英] Programmatically retrieve SQL Server stored procedure source that is identical to the source returned by the SQL Server Management Studio gui?

问题描述

关于如何以编程方式从 SQL Server 2005 获取完全相同的存储过程源的任何指示，就像我在 SQL Server Management Studio 中右键单击该存储过程并选择修改一样?

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?

我正在尝试使用 SMO，但存在一些文本差异.该过程始终具有 CREATE，而不是 ALTER，并且标头中存在一些差异，例如我以编程方式获得的版本中缺少 GO.我可以解决这些问题，但也许有更好的方法?

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?

同样，我在 SQL Server 2005 中，使用 SMSE.通过 Visual Studio 8 2008 使用 SMO.

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

更新:得到了一些关于如何检索存储过程的基础知识的答案.我正在寻找的是检索与 GUI 生成的文本相同(或几乎相同)的文本.

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.

例如:对于 sp_mysp，在 Management Studio 中右键单击，选择修改.这会产生:

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]

我想以编程方式获得相同的东西(注意标题中的 GO，以及它是一个 ALTER PROCEDURE 的事实.理想情况下，我希望以最少的编程方式修复检索到的源.

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

推荐答案

EXEC sp_helptext 'your procedure name';

这避免了 INFORMATION_SCHEMA 方法的问题，如果存储过程太长，它就会被切断.

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

更新:David 写道，这与他的 sproc 不同……也许是因为它将行作为记录"返回以保留格式?如果您想以更自然"的格式查看结果，您可以先使用 Ctrl-T(输出为文本)，它应该完全按照您输入的方式打印出来.如果您在代码中执行此操作，那么执行 foreach 以完全相同的方式组合您的结果是微不足道的.

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.

更新 2:这将为源提供CREATE PROCEDURE"而不是ALTER PROCEDURE"，但我知道没有办法让它改用ALTER".不过，这是一件微不足道的事情，不是吗?

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?

更新 3:有关如何在源代码控制系统中维护 SQL DDL(数据库结构)的更多见解，请参阅评论.这才是这个问题的关键.

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.

这篇关于以编程方式检索与 SQL Server Management Studio gui 返回的源相同的 SQL Server 存储过程源?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！