SQL扫描并执行文件夹里的sql脚本

时间：2022-04-04 03:32:56

场景：项目数据库操作全部使用存储过程实现。每天都会有很多存储过程更新/增加，人工对测试环境中存储过程更新，会有一定概率出现遗漏，也麻烦！所以，需要一个工具将文件夹中所有存         储过程执行一次。
实现：首先想到的是用c#，很简单，而且功能强大。
        但是，我想尝试一下使用sql实现同样的功能，然后就发现了xp_cmdshell这个强大而又危险的东西！查询分析器执行，无需传入数据库用户名、密码，传入目录，就会按照脚本文件名排         序执行 （感谢@OK_008提醒有些情况是需要按顺序执行的。）。
原理：利用xp_cmdshell扩展存储过程将命令字符串作为操作系统命令 shell 执行,并以文本行的形式返回所有输出。实现扫描文件夹，并执行存储过程。
未解决：我实现的还只是扫面一个文件夹里的存储过程，这样如果存储过程被分放到几个文件夹，就需要执行几次，很不方便。如果有大神知道，怎么扫描子文件夹里的存储过程，请赐教！
嘿嘿！平时比较懒，很多东西想记录下来，却没有去做，只能写下这样比较省时间的啦！

 USE [xxxx]

 GO

 IF OBJECTPROPERTY(OBJECT_ID(N'ExecDirPro'), N'IsProcedure') = 1

     DROP PROCEDURE dbo.ExecDirPro

 GO

 --------------------------------------------------------------------------------------------

 -- Name:            ExecDirPro

 -- Purpose:            出入目录，执行目录下所有sql脚本。注意传入的目录有中文时，前面加N

 -- Location:        Lottery

 -- Excecution Example:    EXEC dbo.ExecDirPro

 -- Authorized to:

 --

 -- Author:                clq

 -- Create date:            2014-6-26

 -- Alter:

 --------------------------------------------------------------------------------------------

 CREATE PROCEDURE [dbo].[ExecDirPro]

     @dirPath nvarchar(200)

 AS

 begin try

     --开启xp_cmdshell,完成以后关闭。保持开启很危险

     EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 1;RECONFIGURE;

     CREATE TABLE #T([filename] nvarchar(1024));

     declare @path nvarchar(300)

     set @path='dir '+@dirPath+' /b'

     INSERT #T EXEC xp_cmdshell @path

     DELETE #T WHERE [filename] IS NULL;

     DECLARE @FILENAME NVARCHAR(1024);

     DECLARE cur CURSOR FOR SELECT [filename] FROM #T order by [filename];

     OPEN cur;

     DECLARE @sql NVARCHAR(1000);

     FETCH NEXT FROM cur INTO @FILENAME;

     WHILE @@FETCH_STATUS=0

         BEGIN

            SET @sql=N'sqlcmd -E -i '+@dirPath+@FILENAME;

            EXEC xp_cmdshell @sql;

            FETCH NEXT FROM cur INTO @FILENAME;

         END

     CLOSE cur;

     DEALLOCATE cur;

     --关闭xp_cmdshell

     EXEC sp_configure 'show advanced options', 1;RECONFIGURE;

     EXEC sp_configure 'xp_cmdshell', 0;RECONFIGURE;

 end try

 BEGIN CATCH

     --关闭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

 　　

利用xp_cmdshell扩展，调用执行系统shell脚本。没错，开启xp_cmdshell之后，可以执行shell脚本，可以干很多坏事！嘿嘿！如果你的数据库权限被人拿到，并且有开启xp_cmdshell的权限，这是极其危险的！xp_cmdshell一直开着不关，更是要不得的！请一定记住，完毕以后关闭xp_cmdshell!

相关文章