我有一堆存储过程的名称,我想要导出每个存储过程的创建脚本。有什么最好的方法吗?
目前我正在手动在SSMS中选择存储过程并选择 "Script stored procedure as -> Drop and Create to"
。这似乎很繁琐。我希望有更好的方法来处理这个问题。谢谢。
我有一堆存储过程的名称,我想要导出每个存储过程的创建脚本。有什么最好的方法吗?
目前我正在手动在SSMS中选择存储过程并选择 "Script stored procedure as -> Drop and Create to"
。这似乎很繁琐。我希望有更好的方法来处理这个问题。谢谢。
您可以在对象资源管理器中右键单击数据库,然后执行任务 > 生成脚本。
这允许您选择一堆对象进行脚本编写(例如表格、视图、存储过程),并将其存储到单个大型SQL文件中,或者每个对象一个SQL文件。效果非常好!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
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('')
LIKE
并使用in
代替。 - Martin Smithsys.objects
获取SP列表的方法,但是在sys.sql_modules
中没有日期列... - Azimuth类似这样的东西可以帮助您。
使用动态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
如果您选择“查看”->“摘要”
然后从对象资源管理器中点击“存储过程”,它将为您提供所有存储过程的列表,您可以使用Ctrl / Shift选择(选择多个)。 然后从那里,您可以一次创建所有删除脚本,然后再创建所有的创建脚本。这是我在SSMS中发现的许多怪癖之一。
注意:另一个很棒的功能是筛选选项,可以轻松筛选您的存储/表程序。只需右键单击对象资源管理器即可调出菜单。
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。
你可以查看 sp_helptext,了解如何利用它来创建脚本的一些想法。
Visual Studio 2008 数据库专业版和 Visual Studio 2010 专业版(及以上版本)支持 SQL Server 2005/2008 的特殊项目类型。这些项目支持自动创建更改脚本,包含当前项目与指定目标数据库之间的所有更改。
据我所知,RedGate 也提供了类似的工具,但我没有使用过它们。
-- 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