how to execute all .sql file of current and sub folder using xp_cmdshell

I am using sql server开发者_StackOverflow中文版 2008 , I am developing script that get all .sql file of given path ( also serch in subfolder recursively). Thanks in advance.

You could use a batch file like this. Call it ashwin.bat (or whatever you like) and it will look for all the files in C:\tmp\so\ashwin that have a .sql extension and then invokes sqlcmd against all of those files against a named instance database of localhost\localsqla and runs them in the master database.

@echo off
For /R "C:\tmp\so\ashwin\"  %%i in (*.sql) DO CALL sqlcmd.exe -E -S localhost\localSQLA -d master  -i %%i

A litle enhancement for logging purposes:

@echo off
For /R "C:\Deploy\SQL"  %%i in (*.sql) DO CALL echo %%i && sqlcmd.exe -E -S DB_IP -d DATABASE -i %%i -j