t-sql存储过程创建脚本

I have a bunch of stored procedure names. I want to export the create script for each of the stored procedure. What is the best way to do it?

我有一堆存储过程名称。我想导出每个存储过程的创建脚本。最好的方法是什么？

Right now I am manually selecting the stored proc in SSMS and selecting "Script stored procedure as -> Drop and Create to". This seems tedious. I am hoping there is a better way to deal with this. Thanks.

现在我在SSMS中手动选择存储过程并选择“脚本存储过程为 - >删除和创建到”。这看起来很乏味。我希望有更好的方法来解决这个问题。谢谢。

8 个解决方案

#1

You can right-click on the database in the Object Explorer and do a Task > Generate Scripts.

您可以在对象资源管理器中右键单击数据库，然后执行“任务”>“生成脚本”。

alt text http://i25.tinypic.com/2khmr8.png

替代文字http://i25.tinypic.com/2khmr8.png

That allows you to pick a whole bunch of objects to be scripted (e.g. tables, views, stored procs) and you can store those into a single big SQL file, or one SQL file per object. Works really quite well!

这允许您选择要编写脚本的大量对象（例如，表，视图，存储过程），并且可以将这些对象存储到单个大SQL文件中，或者每个对象存储一个SQL文件。工作真的很好！

Update: if you want to do this in the SQL Server Management Studio app, you can use this SQL script to find the stored procs and their definitions - you cannot however have SQL Server Mgmt Studio write out the files to disk, that doesn't work - but you can copy the results into e.g. Excel.

更新：如果要在SQL Server Management Studio应用程序中执行此操作，可以使用此SQL脚本查找存储的过程及其定义 - 但是，您无法将SQL Server Mgmt Studio写入磁盘，而不是工作 - 但您可以将结果复制到例如Excel中。

SELECT 
    pr.name ,
    pr.type_desc ,
    pr.create_date ,
    mod.definition
FROM sys.procedures pr
INNER JOIN sys.sql_modules mod ON pr.object_id = mod.object_id
WHERE pr.Is_MS_Shipped = 0

#2

To script out all ones matching a particular criteria you could use something like the below.

要编写符合特定条件的所有符号，您可以使用类似下面的内容。

DECLARE @t VARCHAR(max) = '';
SELECT @t = @t +
 'If OBJECT_ID(''' + QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' +  QUOTENAME(OBJECT_NAME(object_id)) + ''',''p'') IS NOT NULL
    DROP PROCEDURE ' + QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' +  QUOTENAME(OBJECT_NAME(object_id)) + '
GO

SET ANSI_NULLS '
            + CASE
                WHEN uses_ansi_nulls = 1 THEN 'ON'
                ELSE 'OFF'
              END
            + '
GO

SET QUOTED_IDENTIFIER '
            + CASE
                WHEN uses_quoted_identifier = 1 THEN 'ON'
                ELSE 'OFF'
              END
            + '
GO

' + definition + ' 
GO

'
FROM   [sys].[sql_modules]
WHERE  OBJECTPROPERTY (object_id,'IsProcedure' )=1
       AND OBJECT_NAME(object_id) LIKE '%some_patttern%'


/*Stops the long text from getting truncated in SSMS*/
SELECT @t AS [processing-instruction(x)]
FOR XML PATH('')

#3

If you select View --> Summary

如果选择“查看” - >“摘要”

Then Click "Stored procedures" from the object explorer it will provide you with a list of all your stored procedures which you can Ctrl/Shift select (select multiples). Then from there you can create all the drop scripts at once then all the create scripts. This is one of the many quirks I've found with SSMS.

然后单击对象资源管理器中的“存储过程”，它将为您提供所有存储过程的列表，您可以按Ctrl / Shift选择（选择多个）。然后，您可以从那里创建所有的拖放脚本，然后创建所有创建脚本。这是我在SSMS中发现的众多怪癖之一。

Note: Another neat feature is the filter option, allowing you to filter through your stored/tables procedures with ease. Simply right click in the object explorer to bring up the menu.

注意：另一个简洁的功能是过滤选项，允许您轻松过滤存储/表格过程。只需右键单击对象资源管理器即可调出菜单。

#4

Something like this would help you.

这样的事情会对你有所帮助。

Using dynamic sql and cursors you can generate script, each in a separate file with .sql extension.

使用动态sql和游标，您可以生成脚本，每个脚本都在一个扩展名为.sql的单独文件中。

This script will generate script for all procedures whose names are mention in IN clause:

此脚本将为IN子句中提及其名称的所有过程生成脚本：

DECLARE @name varchar(100)
DECLARE @Definition varchar(max)
DECLARE @sql varchar(300)
DECLARE @schema varchar(10)
CREATE TABLE TEMPTABLE (ID INT IDENTITY(1,1), def varchar(max))
DECLARE script CURSOR  
FOR
SELECT OBJECT_NAME(SYS.SQL_MODULES.OBJECT_ID), [DEFINITION] FROM SYS.SQL_MODULES 
INNER JOIN SYS.OBJECTS ON SYS.OBJECTS.OBJECT_ID = SYS.SQL_MODULES.OBJECT_ID WHERE SYS.OBJECTS.TYPE='P'
--AND [NAME] IN('SOME PROC 1','SOME PROC 2','SOME PROC 3','SOME PROC 4') --<------ Mention Proc names you want to generate scripts for
OPEN script
FETCH NEXT FROM script INTO @name, @Definition
WHILE @@FETCH_STATUS = 0 
BEGIN
  FETCH NEXT FROM script INTO @name, @Definition
  SET @schema = (select SYS.SCHEMAS.[NAME] from SYS.OBJECTS 
                 INNER JOIN SYS.SCHEMAS ON SYS.OBJECTS.SCHEMA_ID = SYS.SCHEMAS.SCHEMA_ID 
                 WHERE SYS.OBJECTS.[NAME]='' + @name + '')
  SET @sql = 'IF EXISTS (SELECT * FROM ' + (@schema) +
             '.PROCEDURES WHERE [NAME] = ''' + @name + ''')' + CHAR(10)
  SET @sql = @sql + 'DROP PROCEDURE ' + @schema + '.' + @name + CHAR(10) + 'GO' + CHAR(10)
  PRINT @sql
  INSERT INTO TEMPTABLE VALUES(@sql + @definition)
  SET @Sql = ('BCP "SELECT TOP 1 def FROM TEMPTABLE ORDER BY ID DESC" queryout "D:\' + @name + '.sql" -c -T')
  EXEC XP_CmdShell @Sql  --<---- Export to file
END 
CLOSE script
DEALLOCATE script
SELECT * FROM TEMPTABLE  --<----------- Optional
DROP TABLE TEMPTABLE

#5

You might look at sp_helptext for some ideas about how you can leverage that to create your scripts.

您可以查看sp_helptext以获取有关如何利用它来创建脚本的一些想法。

#6

Visual Studio 2008 Database Professional Edition and Visual Studio 2010 Professional (and above) supports special project types for SQL Server 2005/2008. These projects support the automatic creation of change scripts, containing all changes between the current project and a specified target database.

Visual Studio 2008数据库专业版和Visual Studio 2010 Professional（及更高版本）支持SQL Server 2005/2008的特殊项目类型。这些项目支持自动创建更改脚本，包含当前项目与指定目标数据库之间的所有更改。

AFAIK RedGate also provides some tools like this, though, I don't have any experiences with them.

AFAIK RedGate也提供了一些像这样的工具，但我没有任何经验。

#7

The following will generate scripts for a set of stored procedure names. The scripts will be saved as a series of .sql files. Insert the names of the procedures to script into @Table.

以下内容将为一组存储过程名称生成脚本。脚本将保存为一系列.sql文件。将脚本的过程名称插入@Table。

-- Script Multiple SPROC as drop and create

-- SPROCS to script
DECLARE @Table TABLE (Name VARCHAR(MAX));
INSERT INTO @Table (Name) VALUES ('StoredProcedure1'), ('StoredProcedure2')

-- Drop TempTable if extant: http://*.com/questions/659051/check-if-a-temporary-table-exists-and-delete-if-it-exists-before-creating-a-temp
IF OBJECT_ID('tempdb..##Temp') IS NOT NULL DROP TABLE dbo.##Temp

-- Loop through SPROCs    
-- Cursor: https://www.mssqltips.com/sqlservertip/1599/sql-server-cursor-example/
-- Substring: http://*.com/questions/4662496/trim-left-characters-in-sql-server
DECLARE @item VARCHAR(MAX); -- SPROC Name
DECLARE db_cursor CURSOR FOR  
SELECT Name FROM @Table WHERE 1=1

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @item

WHILE @@FETCH_STATUS = 0   
BEGIN
    -- Execute
    -- Print to file using Temp Table: http://*.com/questions/10568975/can-i-specify-an-input-sql-file-with-bcp
    -- Script SPROC via XML: http://*.com/a/3292693/5301903
    -- ANSI_NULLS and QUOTED_IDENTIFIER retain current settings.
    -- Prepare Temp Table
    SELECT
'IF EXISTS(SELECT * FROM sys.procedures WHERE Name = '''+Object_name(object_id)+''')
    DROP PROCEDURE [dbo].['+Object_name(object_id)+']
GO

SET ANSI_NULLS '+CASE WHEN CAST(ISNULL(OBJECTPROPERTYEX(object_id,N'ExecIsAnsiNullsOn'),0) AS bit) = 1 THEN 'ON' ELSE 'OFF' END+'
GO

SET QUOTED_IDENTIFIER '+CASE WHEN CAST(ISNULL(OBJECTPROPERTYEX(object_id,N'ExecIsQuotedIdentOn'),0) AS bit) = 1 THEN 'ON' ELSE 'OFF' END+'
GO

' + definition + ' 
GO' AS Text
INTO dbo.##Temp
FROM [sys].[sql_modules] 
WHERE  1=1
--AND OBJECTPROPERTY (object_id,'IsProcedure' )=1
--AND OBJECTPROPERTY (object_id,'IsTable' )=1
--AND Object_name(object_id) LIKE @name
AND Object_name(object_id) = @item

    -- Print to text https://social.msdn.microsoft.com/Forums/en-US/71aefd98-ee46-48fe-a129-60791c583555/output-results-to-text-file-using-tsql?forum=transactsql
    DECLARE @CMD VARCHAR(1000) = 'bcp "SELECT * FROM ##Temp" queryout C:\temp\'+@item+'.sql -c -T -S '+ @@servername
    EXECUTE Master.dbo.xp_CmdShell  @Cmd

    -- Clean up
    DROP TABLE dbo.##Temp

    -- End Execute
    FETCH NEXT FROM db_cursor INTO @item
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

#8

C:\>BCP "Select '--'+pr.name,
    pr.type_desc, pr.create_date, pr.modify_date, CHAR(13)+mod.definition
    from DATABASE_NAME.sys.objects pr
    INNER JOIN DATABASE_NAME.sys.sql_modules mod ON pr.object_id = mod.object_id
    where type='p'" queryout "C:/output.sql" -c -T -S SERVER_NAME

Execute from command prompt... it will return all stored procedures in one file with time stamps on with SP with created/modified date

从命令提示符执行...它将在一个文件中返回所有存储过程，并带有带有创建/修改日期的SP的时间戳

#1