备份 SQL SP 和函数的快速方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/707328/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Quick way to backup SQL SP and Functions?
提问by David.Chu.ca
I have a long list of SPs (stored procedure) and Functions in my SQL server db. I could save them one by one by right clicking and script XXX to Alter To. Is there any way in TSQL to query all SPs and functions save them to xxx.sql files?
我的 SQL 服务器数据库中有很长的 SP（存储过程）和函数列表。我可以通过右键单击和脚本 XXX 将它们一一保存到 Alter To。TSQL中是否有任何方法可以查询所有SP并将它们保存到xxx.sql文件中？
For example, for sp_mySP1, I would like to save it to sp_mySP1.sql which is a text file. The database is too big and I would like save only SPs and functions as a backup of source codes.
例如，对于 sp_mySP1，我想将其保存到文本文件 sp_mySP1.sql。数据库太大，我只想保存 SP 和函数作为源代码的备份。
回答by Marc Gravell
In management studio; find the database, right-click, tasks, generate scripts;
在管理工作室；找到数据库，右键，tasks，生成脚本；
next-next-next until you "Choose Object Types". Select "Stored procedures" and "User-defined functions", next, Select All; choose an output; go!
下一个下一个直到您“选择对象类型”。选择“存储过程”和“用户定义函数”，下一步，全选；选择一个输出；走！
回答by Gordon Bell
1) Right-click on your Database name in the Object Explorer
1) 在对象资源管理器中右键单击您的数据库名称
2) Select "Tasks > Generate Scripts..." from the Context menu
2) 从上下文菜单中选择“任务 > 生成脚本...”
3) Select your Database in the list and click Next
3) 在列表中选择您的数据库，然后单击下一步
4) Click Next on the Chose Script Options
4）在选择脚本选项上单击下一步
5) In Object Types, check Stored Procedures and User-defined functions, click Next
5）在对象类型中，勾选存储过程和用户定义函数，点击下一步
6) Click Select All on the Stored Procedures selection screen, click Next
6) 在 Stored Procedures 选择屏幕上单击 Select All，单击 Next
7) Click Select All on the Functions selection screen, click Next
7) 在功能选择屏幕上单击全选，单击下一步
8) Select 'Script to New Query Window' and click Finish
8) 选择'Script to New Query Window'并点击Finish
回答by Jamie
Here's a proc that will export SOME types of data.
这是一个将导出某些类型数据的过程。
if exists ( select * from sysobjects where name = 'ExportData_P' )
drop proc ExportData_P
go
CREATE PROC dbo.ExportData_P (
@tableName varchar(500),
@where varchar(5000) = '(1=1)'
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @sql varchar(8000)
DECLARE @fieldList varchar(8000)
DECLARE @valueList varchar(8000)
SELECT @fieldList = '', @valueList = ''
DECLARE @cols TABLE ( column_name nvarchar(250), data_type varchar(250) )
DECLARE @c nvarchar(250), @data_type varchar(250)
INSERT INTO @cols
select column_name, data_type
from information_Schema.columns
where table_name = @tableName
WHILE EXISTS ( SELECT TOP 1 * FROM @cols )
BEGIN
SELECT TOP 1 @c = column_name, @data_type = data_type FROM @cols
SELECT
@fieldList = @fieldList + @c + ', ',
@valueList = @valueList + CHAR(13) + 'case when ' + @c + ' is null then ''NULL'' else '''''''' + ' +
case when @data_type in ('text','ntext','char', 'nvarchar', 'varchar' ) then
' REPLACE ( REPLACE ( REPLACE ( '
else ''
end +
'IsNull ( convert(varchar' +
( -- change this section to pass the length of varchar to convert
case when @data_type in ( 'uniqueidentifier' ) then '(50)'
when @data_type in ( 'text', 'ntext' ) then '(8000)'
else '' end
) +
', ' +
@c +
'), '''' )' + -- end is null
case when @data_type in ('text','ntext','char', 'nvarchar', 'varchar' ) then
', CHAR(39), CHAR(39)+CHAR(39) ), CHAR(13), '''' + CHAR(13) + ''''), CHAR(9), '''' + CHAR(9) + '''') '
else ''
end +
' + '''''''' end + '', '' + '
DELETE FROM @cols WHERE column_name = @c
END
SELECT @fieldList = LEFT ( @fieldList, LEN(@fieldList)-1 ),
@valueList = LEFT ( @valueList, LEN(@valueList)-1 )
SELECT @sql = 'select ''insert into ' + @tableName + ' (' + @fieldList + ') ' +
' VALUES ( ''+ ' + left ( @valueList, len(@valueList)-5) + ''') '' from ' + @tableName +
' WHERE ' + @where
-- into [#mcoe_temp_export' + @tableName + ']
print @sql
EXEC ( @sql )
--EXEC ( 'select * from [#mcoe_temp_export' + @tableName + ']' )
SET NOCOUNT OFF
END
go
Use like:
像这样使用：
exec ExportData_P 'tablename'
回答by Jamie
you could query syscomments to get your sql object creation text, but I don't know how to save them all in separate files using just TSQL.
您可以查询 syscomments 以获取您的 sql 对象创建文本，但我不知道如何仅使用 TSQL 将它们全部保存在单独的文件中。
select * from syscomments
