我有一堆存储过程的名称，我想要导出每个存储过程的创建脚本。有什么最好的方法吗？
目前我正在手动在SSMS中选择存储过程并选择 "Script stored procedure as -> Drop and Create to"。这似乎很繁琐。我希望有更好的方法来处理这个问题。谢谢。
T-SQL 存储过程创建脚本
19
- stackoverflowuser
8个回答
24
您可以在对象资源管理器中右键单击数据库，然后执行任务 > 生成脚本。
更新：如果您想在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
- marc_s
1
谢谢Marc。实际上我有一堆SSRS报告和一个提取存储过程名称的工具。由于将会有更多这些报告以频繁的间隔到来，我在考虑编写一个T-SQL脚本，可以获取存储过程列表并生成一个脚本文件。这是否可行？ - stackoverflowuser
12
要脚本化所有符合特定条件的内容，您可以使用以下类似的方法。
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('')
- Martin Smith
5
这是如何工作的？我需要将@t设置为逗号分隔的存储过程名称列表吗？ - stackoverflowuser
@stack - 不过，你可以摆脱
LIKE并使用in代替。 - Martin Smith太棒了，正是我所需要的，而且不需要鼠标点击。 - dividius
如何针对某个日期之后创建的存储过程执行相同操作？我已经找到了一种从
sys.objects获取SP列表的方法，但是在sys.sql_modules中没有日期列... - Azimuth很棒的解决方案。将此脚本添加到我的工具箱中。 - Adam Plocher
2
类似这样的东西可以帮助您。
使用动态SQL和游标，您可以生成脚本，并将每个脚本保存在单独的.sql文件中。
此脚本将为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
- NeverHopeless
2
如果您选择“查看”->“摘要”
然后从对象资源管理器中点击“存储过程”，它将为您提供所有存储过程的列表，您可以使用Ctrl / Shift选择（选择多个）。 然后从那里，您可以一次创建所有删除脚本，然后再创建所有的创建脚本。这是我在SSMS中发现的许多怪癖之一。
注意：另一个很棒的功能是筛选选项，可以轻松筛选您的存储/表程序。只需右键单击对象资源管理器即可调出菜单。
- Gage
1
感谢您告知筛选功能。我点了个赞。 - stackoverflowuser
1
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
在命令提示符下执行...它将返回一个文件中的所有存储过程，其中包括带有创建/修改日期的时间戳的SP。
- Felice
1
你可以查看 sp_helptext，了解如何利用它来创建脚本的一些想法。
- HLGEM
1
Visual Studio 2008 数据库专业版和 Visual Studio 2010 专业版（及以上版本）支持 SQL Server 2005/2008 的特殊项目类型。这些项目支持自动创建更改脚本，包含当前项目与指定目标数据库之间的所有更改。
据我所知，RedGate 也提供了类似的工具，但我没有使用过它们。
- Florian Reischl
1
以下内容将生成一组存储过程名称的脚本，并将这些脚本保存为一系列 .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: https://dev59.com/DnRB5IYBdhLWcg3wUV1d
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: https://dev59.com/jm445IYBdhLWcg3w3N2z
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: https://dev59.com/IWkv5IYBdhLWcg3wewxd
-- Script SPROC via XML: https://dev59.com/KXA75IYBdhLWcg3wdIp0#3292693
-- 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
- Hans Vonn
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接