以编程方式检索与 SQL Server Management Studio gui 返回的源相同的 SQL Server 存储过程源？

Question

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

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

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

更新：获得了一些答案，讲述了如何检索存储过程的基础知识。 我正在寻找的是检索与 GUI 生成的文本相同（或几乎相同）的文本。

示例：对于 sp_mysp，在 Management Studio 中右键单击，选择修改。 这会生成：

    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 的事实。理想情况下，我希望通过对检索到的源进行最少的编程修复来获得此结果。

我我们很乐意只得到脚本日期细节上的不同内容。

Answer 1

EXEC sp_helptext 'your procedure name';

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

更新：大卫写道，这与他的存储过程不同......也许是因为它将行作为“记录”返回以保留格式？ 如果您想以更“自然”的格式查看结果，可以先使用 Ctrl-T（以文本形式输出），它应该按照您输入的内容准确地打印出来。 如果您在代码中执行此操作，则执行 foreach 以完全相同的方式将结果组合在一起是很简单的。

更新2：这将为源提供“CREATE PROCEDURE”而不是“ALTER PROCEDURE”，但我知道没有办法让它使用“ALTER”来代替。 不过，这是一件微不足道的事情，不是吗？

更新 3：请参阅评论，了解有关如何在源代码控制系统中维护 SQL DDL（数据库结构）的更多见解。 这确实是这个问题的关键。

Answer 2

您必须手动对其进行编码，SQL Profiler 会显示以下内容。

SMSE 在生成语句时执行相当长的查询字符串。

以下查询（或类似的查询）用于提取文本：

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')

它返回纯 CREATE，然后在代码中的某处用 ALTER 替换。

SET ANSI NULL 内容以及 GO 语句和日期都在此前面。

使用 sp_helptext，它更简单......

Answer 3

你说的是程序化吧？ 我希望C#没问题。 我知道您说过您尝试过 SMO，但它并没有完全满足您的要求，因此这可能不太适合您的请求，但它会以编程方式读出合法的 SQL 语句，您可以运行这些语句来重新创建存储过程。 如果它没有您想要的 GO 语句，您可能可以假设 StringCollection 中的每个字符串都可以有一个 GO在它之后。 您可能不会得到带有日期和时间的评论，但在我听起来类似的项目（必须单独备份所有内容的大部署工具）中，这做得相当好。 如果您有想要使用的先前基础，并且仍然有原始数据库可以运行它，那么我会考虑放弃最初的工作并重新标准化此输出。

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;

Answer 4

使用以下 select 语句来获取整个定义：

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

我猜 SSMS 和其他工具会读出此内容并在必要时进行更改，例如将 CREATE 更改为 ALTER。 据我所知，SQL 不存储过程的其他表示形式。

Answer 5

我同意马克的观点。 我将输出设置为文本模式，然后 sp_HelpText 'sproc'。 我将其绑定到 Crtl-F1 以使其变得简单。

Answer 6

数据库发布向导可以转储架构（和其他对象）从命令行。

Answer 7

我只是想指出，您最好使用拖放，而不是使用查找和替换来更改创建过程来更改过程，您可以将其放在顶部，并且它确实需要文本搜索。

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

如果你确定它在那里，我想你也可以把它扔掉，但我不建议这样做。 不要忘记 go，因为 create procedure 必须是批处理中的第一个也是唯一一个语句。

或者懒惰的方法：

IF OBJECT_ID(N'sp_name') is not null
    DROP sp_name
GO

Answer 8

我通过 链接看到一篇文章< /a>.
有四种方法，我只是在这里做了一个简短的总结，以帮助其他程序员。

1. 执行 sp_helptext 'sp_name';

2. 选择 OBJECT_ID('sp_name')

3. 选择 OBJECT_DEFINITION(
   OBJECT_ID('sp_name')
   ) AS [定义];

4. 从sys.sql_modules中选择*，其中object_id = object_id('sp_name');

Answer 9

要更改存储过程，以下是 C# 代码：

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();

注意事项：

1. 确保连接字符串中的 USER 有权更改 SP
2. 从存储过程中删除所有 GO,SET ANSI_NULLS XX,SET QUOTED_IDENTIFIER 语句脚本文件。 （如果不这样做，SqlCommand 将抛出错误）。