通过SQL脚本导出存储过程

Question

是：如何使用查询备份选定的存储过程

我想通过命令行备份 200 个存储过程中的 10 个（在 SQL Server Management Studio 中）。有简单的方法吗？

现在，我正在使用“数据库”->“任务”->“生成脚本”选项，该选项引导我完成一系列对话框，在其中选择要导出的 SP。我想让这个过程变得简单，这样我就不必再重复一遍。

注意：我所说的导出是指将其打印在屏幕上，以便我可以复制它并将其保存在文本文件中。

Answer 1

使用 INFORMATION_SCHEMA.Routines 怎么样？

DECLARE MY_CURSOR Cursor
FOR
SELECT r.Routine_Definition
FROM INFORMATION_SCHEMA.Routines r 
OPEN MY_CURSOR
    DECLARE @sproc VARCHAR(MAX) 
    FETCH NEXT FROM MY_CURSOR INTO @sproc
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
        IF (@@FETCH_STATUS <> -2)
        PRINT @sproc
        FETCH NEXT FROM MY_CURSOR INTO @sproc
    END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
GO

---

编辑

听起来您可能希望这样的内容在结果集中包含 LAST_ALTERED 日期和定义。

SELECT
    r.LAST_ALTERED,
    r.ROUTINE_NAME,
    r.Routine_Definition
FROM INFORMATION_SCHEMA.Routines r 

Answer 2

您可以使用以下查询选择所需的 SP：

SELECT obj.Name as SPName,

modu.definition as SPDefinition,

obj.create_date as SPCreationDate

FROM sys.sql_modules modu

INNER JOIN sys.objects obj

ON modu.object_id = obj.object_id

WHERE obj.type = 'P'  AND obj.Name IN ('sp1','sp2', ect)

另请参阅：http://www.sqlservercurry.com/2009/03/list-all-stored-procedures-of-database.html 和 http://www.sqlservercurry.com/2007/12 /redirect-select-query-output-to-text.html

Answer 3

以下 SQL 应该可以完成您想要的操作。

SET NOCOUNT ON

DECLARE @procs AS TABLE( object_id INT
                        , definition NVARCHAR(MAX)
                        , uses_ansi_nulls BIT
                        , uses_quoted_identifier BIT
                        )
INSERT INTO @procs
SELECT m.object_id
     , m.definition
     , m.uses_ansi_nulls
     , m.uses_quoted_identifier
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
    ON m.object_id = o.object_id
WHERE o.type = 'P'

--Change this part to suit your needs...
AND o.name IN ('sproc1'
              ,'sproc2'
              ,'sproc3'
              )
--Optionally filter by date?
--AND o.create_date >= '02/01/2012'


DECLARE @endStmt NCHAR(6)
      , @object_id INT
      , @definition NVARCHAR(MAX)
      , @uses_ansi_nulls BIT
      , @uses_quoted_identifier BIT
SELECT @object_id = MIN(object_id)
     , @endStmt = CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
FROM @procs

WHILE ISNULL(@object_id,0) > 0
BEGIN
    SELECT @definition = definition
         , @uses_ansi_nulls = uses_ansi_nulls
         , @uses_quoted_identifier = uses_quoted_identifier
    FROM @procs

    IF @uses_ansi_nulls = 1
        PRINT 'SET ANSI_NULLS ON' + @endStmt
    ELSE
        PRINT 'SET ANSI_NULLS OFF' + @endStmt

    IF @uses_quoted_identifier = 1
        PRINT 'SET QUOTED_IDENTIFIER ON' + @endStmt
    ELSE
        PRINT 'SET QUOTED_IDENTIFIER OFF' + @endStmt

    IF LEN(@definition) <= 4000
        PRINT @definition 
    ELSE
    BEGIN
        DECLARE @crlf VARCHAR(2), @len BIGINT, @offset BIGINT, @part BIGINT
        SELECT @crlf = CHAR(13)+CHAR(10)
             , @len = LEN(@definition)
             , @offset = 1
             , @part = CHARINDEX(@crlf,@definition)-1

        WHILE @offset <= @len
        BEGIN
            PRINT SUBSTRING(@definition,@offset,@part)

            SET @offset = @offset + @part + LEN(@crlf)
            SET @part = CHARINDEX(@crlf,@definition,@offset)-@offset  
        END
    END

    PRINT @endStmt


    SELECT @object_id = MIN(object_id)
    FROM @procs
    WHERE object_id > @object_id
END

如果将上述 SQL 保存为名为 BackUpSprocs.SQL 的文件，则可以运行类似于以下内容的命令将输出获取到文件。

SQLCMD -E -S SQLSERVER_NAME -d DATABASE_NAME -i BackUpSprocs.SQL -o Sprocs.txt

Answer 4

declare @S nvarchar(max) 

set @S = N''
select @S = @S + [definition] + 
                 nchar(13) + nchar(10) + 
                 N'GO' + 
                 nchar(13) + nchar(10)
from sys.sql_modules as m
  inner join sys.objects as o
    on m.object_id = o.object_id
where o.create_date > '20120101' and
      o.name in ('Proc1', 'Proc2', 'ProcN')

select @S
for xml path('')

单击结果窗格中的链接可查看整个脚本。

Answer 5

SELECT SO.Name as Name, 

SM.definition as SPDefinition, 

obj.create_date as CreationDate 

FROM sys.sql_modules SM

INNER JOIN sys.objects SO

ON SM.object_id = SO.object_id 

WHERE SO.type = 'P'  AND SO.create_date >= '01-01-2012'

看看这个。这可能对你有帮助。

Answer 6

不是 SQL 方式，但我认为您可以使用 自动化数据库->任务->生成脚本选项/...更多步骤 .com/site/" rel="nofollow">autoit 或 Sikuli 或者其他一些 GUI 测试工具。

我在网络上尝试了一些 Sikuli，但它对我来说并不是 100% 可靠。

AutoIt v3 是一种类似 BASIC 的免费软件脚本语言，专为
自动化 Windows GUI 和一般脚本编写。

Answer 7

我编辑了 MyItchyChin 的脚本，因为我发现其中存在一些缺陷。当 @part < 时，它将无限循环。 0 并且无法正确打印关闭过程代码的“END”关键字（“create procedure ... as begin...END”）。我还做了其他一些小的改变。我的问题现在已经解决了！非常感谢 MyItchyChin 提供的初始脚本。

Obs：我在 SQL Server 2008 R2 中使用这个脚本。该脚本还可用于编写函数脚本。

 SET NOCOUNT ON

DECLARE @procs AS TABLE( nome varchar(200),object_id INT
                        , definition NVARCHAR(MAX)
                        , uses_ansi_nulls BIT
                        , uses_quoted_identifier BIT
                        )
INSERT INTO @procs
SELECT o.name
     ,m.object_id
     , m.definition
     , m.uses_ansi_nulls
     , m.uses_quoted_identifier
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
    ON m.object_id = o.object_id
WHERE 1=1
--and o.type = 'P'
AND o.name IN ('proc1')



DECLARE @endStmt NCHAR(6)
      , @object_id INT
      , @definition NVARCHAR(MAX)
      , @uses_ansi_nulls BIT
      , @uses_quoted_identifier BIT

DECLARE @crlf VARCHAR(2), @len BIGINT, @offset BIGINT, @part BIGINT

SELECT @object_id = MIN(object_id)
     , @endStmt = CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
FROM @procs

declare c cursor for SELECT definition
,  uses_ansi_nulls
,  uses_quoted_identifier
FROM @procs
order by nome asc

open c
fetch next from c into @definition,@uses_ansi_nulls,@uses_quoted_identifier

while @@fetch_status<>-1
begin
    IF @uses_ansi_nulls = 1
        PRINT 'SET ANSI_NULLS ON' + @endStmt;
    ELSE
        PRINT 'SET ANSI_NULLS OFF' + @endStmt;

    IF @uses_quoted_identifier = 1
        PRINT 'SET QUOTED_IDENTIFIER ON' + @endStmt;
    ELSE
        PRINT 'SET QUOTED_IDENTIFIER OFF' + @endStmt;

    --PRINT @definition;

    IF LEN(@definition) <= 4000
        PRINT @definition
    ELSE
    BEGIN

        SELECT @crlf = CHAR(13)+CHAR(10)
             , @len = LEN(@definition)
             , @offset = 1
             , @part = CHARINDEX(@crlf,@definition)-1

        WHILE @offset <= @len AND @part>=0
        BEGIN

            --PRINT @offset
            --PRINT @part
            --PRINT LEN(@crlf)
            --PRINT @len

            PRINT SUBSTRING(@definition,@offset,@part)

            SET @offset = @offset + @part + LEN(@crlf)
            SET @part = CHARINDEX(@crlf,@definition,@offset)-@offset

            --PRINT @offset
            --PRINT @part
            --PRINT @len

            IF @part < 0 
            PRINT SUBSTRING(@definition,@offset,100)
        END
    END

    PRINT @endStmt;


fetch next from c into @definition,@uses_ansi_nulls,@uses_quoted_identifier
end
close c
deallocate c

Answer 8

我创建了以下过程，该过程遍历所有 SP 和视图，特别是 DB（可以扩展到函数，...），并将每个代码脚本一一存储到 TXT 文件中。在 MS SQL 2008 R2 和 2014 上进行测试

第一部分将 SP 和视图的所有脚本插入临时表。然后稍后使用 BCP 实用程序。如果您愿意，可以使用导出 SSIS 包，而不是像我在本示例中所做的那样使用 SP。

DECLARE  @File_name AS VARCHAR(255)
        ,@Folder_path AS VARCHAR(255)
        ,@File_Path_Name AS VARCHAR(255)
        ,@CMD AS VARCHAR(8000)

IF OBJECT_ID('tempdb..#TEMP_AA') IS NOT NULL DROP TABLE #TEMP_AA;
SELECT 
 T1.NAME AS ObjectName
,T1.type AS ObjectType
,STUFF(((SELECT ' ' + T.[TEXT]
         FROM (SELECT SC.[id],SC.colid,SC.[TEXT]
               FROM SYSCOMMENTS sc
               ) AS T
         WHERE T.[id] = T1.[id]
         ORDER BY T.colid
         FOR XML PATH(''),TYPE
         ).value('.[1]', 'NVARCHAR(MAX)')
         ), 1, 1, '')
 AS ObjectText
INTO #TEMP_AA
FROM SYSOBJECTS AS T1
WHERE 1=1
  AND T1.type IN ('P', 'V') /* Procedures and Views*/
  AND NOT T1.[name] LIKE 'dt_%'

循环遍历临时表，创建带有前缀 P_ 或 V_ 且后缀为日期的文件名，格式为 YYYYMMDD：

-- Exporting Scripts one by one into TXT files
WHILE (SELECT TOP 1 objectName FROM #TEMP_AA) IS NOT NULL
BEGIN
    SELECT TOP 1 
    @File_name = RTRIM(LTRIM(ObjectType)) + '_' + ObjectName +'_' + REPLACE(CAST(CAST(GETDATE()AS DATE) AS VARCHAR),'-','')
    FROM #TEMP_AA;

    IF OBJECT_ID('tempdb..##TEMP_BB') IS NOT NULL DROP TABLE ##TEMP_BB;
    CREATE TABLE ##TEMP_BB (ObjectText VARCHAR(MAX));
    INSERT INTO ##TEMP_BB
    SELECT TOP 1 ObjectText
    FROM #TEMP_AA;

    --'Setting File name'
    SET @Folder_Path = 'C:\AAAA\'
    SET @File_Path_Name = @Folder_Path + @File_name + '.txt'
    SET @CMD ='BCP ##TEMP_BB OUT "'+@File_Path_Name+'" -T -c -t "Your Server"'
     -- 'Output via BCP into TXT file'
    EXEC xp_cmdshell @CMD;

    --Delete Line From temp which has been procese already
    WITH  CTE AS (SELECT TOP 1 *  FROM  #TEMP_AA)
    DELETE FROM CTE;
END