SQL scans and executes SQL scripts in the folder

SQL scan and execute SQL script in folder

Scenario: The project database operations are all implemented using stored procedures. There are a lot of updates/additions to the stored procedures every day, manual updates to the stored procedures in the test environment, there will be a certain probability of omission, also trouble! Therefore, a tool is required to execute all stored procedures in the folder once.
Implementation: The first thought is in C #, very simple, and powerful.
However, I would like to try to use SQL to achieve the same functionality, and then found xp_cmdshell this powerful and dangerous thing! Query Analyzer executes, without passing in the database user name, password, incoming directory, will be executed according to the script file name (thanks to @ok_008 reminders that some situations need to be executed sequentially.) ）。
Principle: Use the xp_cmdshell extended stored procedure to execute the command string as the operating system command shell and return all output as a text line. Implements the scan folder and executes the stored procedure.
Not resolved: I have only implemented a scan of a folder of stored procedures, so if the stored procedure is divided into several folders, it will need to execute several times, very inconvenient. If a great God knows, how to scan the subfolders of the stored procedures, please enlighten!
Hey! Usually more lazy, a lot of things want to record down, but did not do, can only write down so much time-saving!

1  Use [xxxx]2 GO3 IF ObjectProperty(object_id(N'Execdirpro'), N'isprocedure')= 14     DROP PROCEDUREdbo. Execdirpro5 GO6 --------------------------------------------------------------------------------------------7 --Name:execdirpro8 --Purpose: Access directory, execute all SQL scripts under directory. Note that when the incoming directory has Chinese, the front plus n9 --Location:lotteryTen --excecution example:exec dbo. Execdirpro One --Authorized to: A -- - --AUTHOR:CLQ - --Create date:2014-6-26 the --Alter: - --------------------------------------------------------------------------------------------  - CREATE PROCEDURE [dbo].[Execdirpro] -     @dirPath nvarchar( $)     +  as - beginTry +     --turn on the xp_cmdshell and close it when done. It's dangerous to keep it open . A     EXECsp_configure'Show advanced Options',1;RECONFIGURE;EXECsp_configure'xp_cmdshell',1;RECONFIGURE; at     CREATE TABLE#T ([filename] nvarchar(1024x768)); -  -     Declare @path nvarchar( -) -     Set @path='dir'+@dirPath+'/ b' -  -     INSERT#TEXECxp_cmdshell@path in  -     DELETE#TWHERE [filename]  is NULL; to      +     DECLARE @FILENAME NVARCHAR(1024x768); -  the     DECLARECurCURSOR  for SELECT [filename]  from#TOrder  by [filename]; *  $     OPENcur;Panax Notoginseng  -     DECLARE @sql NVARCHAR( +); the     FETCH NEXT  fromCur into @FILENAME; +  A      while @ @FETCH_STATUS=0 the         BEGIN +            SET @sql=N'sqlcmd-e-I.'+@dirPath+@FILENAME;  -            EXECxp_cmdshell@sql;  $            FETCH NEXT  fromCur into @FILENAME; $         END -     CLOSEcur; -     deallocatecur; the     --Close xp_cmdshell -     EXECsp_configure'Show advanced Options',1;RECONFIGURE;Wuyi     EXECsp_configure'xp_cmdshell',0;RECONFIGURE; the EndTry - BEGINCATCH Wu     --Close xp_cmdshell -     EXECsp_configure'Show advanced Options',1;RECONFIGURE; About     EXECsp_configure'xp_cmdshell',0;RECONFIGURE; $     SELECTError_number () aserrornumber; - ENDCATCH; - GO -  　　 

Use the xp_cmdshell extension to invoke the Execute System shell script. Yes, after you turn on xp_cmdshell, you can execute shell scripts to do a lot of bad things! Hey! This is extremely dangerous if you have access to the database and have permission to open xp_cmdshell! xp_cmdshell has been open, but it is not! Please remember to close xp_cmdshell! after completion