MSSQL:您如何使用代码编写存储过程创建脚本? [英] MSSQL: How do you script Stored Procedure creation with code?

问题描述

我正在尝试使用一个数据库中存在但另一个数据库中不存在的 information_schema.routines 来查询存储过程定义列表.

I am trying to query for a list of stored procedure definitions using information_schema.routines that exist in one database but not in another.

SELECT 
    t1.Routine_Definition
FROM
    [server1].MyDatabase.INFORMATION_SCHEMA.Routines t1 
LEFT JOIN
    [server2].MyDatabase.INFORMATION_SCHEMA.Routines t2 ON t1.Routine_Name = t2.Routine_Name
WHERE
    t2.Routine_Name is null

这给了我一行中的查询定义，所以当我有这样的评论时

This gives me the query definitions in a single line so when I have a comment like this

--Some comment
SELECT Column
FROM Somewhere

SQL 被注释掉，我无法使用定义来创建 SP.

The SQL gets commented out and I cannot use the definition to create the SP.

我如何用正确的换行符解析它?
或者
有没有更好的方法来获取这些脚本(使用代码)?

How to I parse this back with the proper line breaks?
or
Is there a better way to get these scripts (using code)?

推荐答案

存储过程在 Management Studio 中只显示在一行.如果您运行查询结果为文本，或使用以下内容，您将获得正确的换行符:

The stored procedure is only displayed on one line in Management Studio. If you run the query with results to text, or use the following, you will get the correct line breaks:

declare @sql varchar(8000) -- varchar(max) in SQL 2005+

SELECT 
        @sql = t1.Routine_Definition
FROM
        INFORMATION_SCHEMA.Routines t1 

print @sql

这篇关于MSSQL:您如何使用代码编写存储过程创建脚本?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！