如何通过批处理将SQL存储过程保存到.sql文件 [英] how save SQL stored procedures to .sql files via batch
问题描述
我已经从devio IT发现了SMOscript，但是它收集了需要一些时间的所有表和sproc。有没有类似的工具，我可以定义哪些sproc出口？此外，我缺少USES子句，SMOScript不会添加到导出的文件，而与作为脚本sproc for the CREATE的manuall导出相反。
最好的问候
sean
使用脚本创建批处理文件（对于格式化抱歉，但实际上应该是内联执行批处理）：
osql -U％1 -P％2 -S％3 -d％4 -h-1 -QSELECT ROUTINE_NAME FROM INFORMATION_SCHEMA。例行程序WHERE ROUTINE_TYPE ='PROCEDURE'-n -osp_list.txt
for（f_list.txt）中的/ f %% a do osql -U％1 -P％2 -S％3 -d％ 4 -h-1 -QSELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_NAME ='%% a'-n -o%% a.sql
将其命名为run.bat。现在，要执行批量使用参数：
run.bat [username] [password] [servername] [database]
例如：
run.bat sa pwd111 localhost\SQLEXPRESS master
首先将所有存储过程名称存储在文件sp_list.txt中，然后逐个存储在单独的脚本文件中。唯一的问题 - 每个脚本的最后一行结果计数 - 我正在工作:)
编辑：查询中的错误
删除受影响的行行
好的，现在我们需要再创建一个批次：
类型％1 | findstr / V / i％2> xxxtmpfile
复制xxxtmpfile％1 / y / v
del xxxtmpfile
命名line_del.bat。看到，第一个参数是要处理的文件，第二个字符串搜索行进行删除。现在修改主批次（再次对格式化感到抱歉）：
osql -U％1 -P％2 -S％3 -d％4 -h-1 -QSELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_TYPE ='PROCEDURE'-n -osp_list.txt
call line_del sp_list.txtrows affected
调用line_del sp_list.txtrow affected
for / f %% a in（sp_list.txt）do osql -U％1 -P％2 -S％3 -d％4 -h-1 -Q SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_NAME ='%% a'-n -o%% a.sql
for / f %% a in（sp_list.txt）do call line_del %% a.sql rows affected
for（f_list.txt）中的/ f %% a请求line_del %% a.sqlrow affected
查看相关文章：
批处理环境中的简单编程命令
osql实用程序
MSSQL：如何使用代码脚本创建存储过程？
删除txt文件中的某些行通过批处理文件
:)您可能会注意到，最后两个来自SO！
I woud like to save my MS SQL Server 2005 stored procedures to .sql files automatically (would prefer a tool which I can call via .bat) so I dont have to click each single sproc manually and save it.
I have already found SMOscript from devio IT, but it gathers all tables and sproc which takes some time. Is there any similar tool where I can define which sproc(s) to export? Also I'm missing the USE clause which SMOScript doesn't add to exported file in contrast to the manuall export as script sproc for CREATE.
best regards sean
Create batch file with script (sorry about formatting, but it's really should be inline to execute batch):
osql -U %1 -P %2 -S %3 -d %4 -h-1 -Q "SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_TYPE = 'PROCEDURE'" -n -o "sp_list.txt"
for /f %%a in (sp_list.txt) do osql -U %1 -P %2 -S %3 -d %4 -h-1 -Q "SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_NAME = '%%a'" -n -o "%%a.sql"
Name it "run.bat". Now, to execute batch use params:
run.bat [username] [password] [servername] [database]
on example:
run.bat sa pwd111 localhost\SQLEXPRESS master
first all stored procedure names will be stored in file sp_list.txt, then one by one in separate script files. The only issue - last line of each script with result count - I'm workin' on it :)
edited: bug in query fixed
Removing "Rows affected" line
Ok, now we need to create one more batch:
type %1 | findstr /V /i %2 > xxxtmpfile
copy xxxtmpfile %1 /y /v
del xxxtmpfile
Name it "line_del.bat". See, the first param is file to process, 2nd - string to search lines for removing. Now modify the main batch (again, sorry about formatting):
osql -U %1 -P %2 -S %3 -d %4 -h-1 -Q "SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_TYPE = 'PROCEDURE'" -n -o "sp_list.txt"
call line_del sp_list.txt "rows affected"
call line_del sp_list.txt "row affected"
for /f %%a in (sp_list.txt) do osql -U %1 -P %2 -S %3 -d %4 -h-1 -Q "SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_NAME = '%%a'" -n -o "%%a.sql"
for /f %%a in (sp_list.txt) do call line_del %%a.sql "rows affected"
for /f %%a in (sp_list.txt) do call line_del %%a.sql "row affected"
See related articles:
Simple programming commands in a batch environment
osql Utility
MSSQL: How do you script Stored Procedure creation with code?
Delete certain lines in a txt file via a batch file
:) you may notice, last two are from SO!
这篇关于如何通过批处理将SQL存储过程保存到.sql文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
