SQL scans and executes SQL scripts in the folder

Scenario: The project database operations are all implemented using stored procedures. There are many stored procedure updates/additions every day, and there is a certain probability of missing stored procedure updates in the test environment, as well as teaching trouble! Therefore, a tool is required to execute all stored procedures in the folder once.
Of course, writing one in C # is also 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! What I've done is to sweep a stored procedure in a folder so that if the stored procedure is divided into several folders, it is inconvenient to do it several times. 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!

Use [Xxxxx]goif objectproperty (object_id (n ' execdirpro '), n ' isprocedure ') = 1DROP PROCEDURE dbo. Execdirpro GO----------------------------------------------------------------------------------------------Name : execdirpro--Purpose: Incoming directory, execute all SQL scripts under directory. --location:lottery--excecution example:exec dbo. Execdirpro--Authorized to:----author:--Create date:2014-6-26--Alter:-------------------------------------------- ------------------------------------------------CREATE PROCEDURE [dbo]. [Execdirpro] @dirPath nvarchar (200)--pay attention to the road to "\" end, when there is Chinese, the front plus n. Asbegin try--turn on xp_cmdshell and close after completion. Keeping it open is dangerous. exec sp_configure ' show advanced options ', 1; RECONFIGURE; EXEC sp_configure ' xp_cmdshell ', 1; RECONFIGURE; CREATE TABLE #T ([filename] nvarchar (1024x768));d eclare @path nvarchar set @path = ' dir ' [email protected]+ ' *.sql/b ' INSERT #T EXEC xp_cmdshell @pathDELETE #T WHERE [filename] is null;declare @FILENAME NVARCHAR (1024x768);D eclare cur CURSOR FO R SELECT [filename] from #T; OPEN cur;declare @sql NVARCHAR (1000); FETCH NEXT from cur to @FILENAME;      While @ @FETCH_STATUS =0begin SET @sql =n ' sqlcmd-e-i ' [email protected][email protected];      EXEC xp_cmdshell @sql; FETCH NEXT from cur to @FILENAME; Endclose cur;deallocate cur;--Close xp_cmdshellexec sp_configure ' show advanced options ', 1; RECONFIGURE; EXEC sp_configure ' xp_cmdshell ', 0; Reconfigure;end Trybegin CATCH--close xp_cmdshell EXEC sp_configure ' show advanced options ', 1;    RECONFIGURE; EXEC sp_configure ' xp_cmdshell ', 0;    RECONFIGURE; SELECT Error_number () as errornumber; END CATCH; 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