Script to run all SQL files in a particular folder
Windows command line processor support the for statement, so you can create a .cmd
file with something like that:
for %%f in (*.sql) do osql -U<User> -P<Pass> -S<Server> -d<Database> -i%%f
This will process all .sq
l files of the current folder sequentially (you can use also sqlcmd as suggested in another answer).
Reference: Microsoft Windows XP - For
Also, you can specify the database directly in the script using at the beginning:
use DatabaseName
go
The go
is important because some statements like CREATE VIEW
or CREATE PROCEDURE
needs to be at the beginning of a batch of SQL commands.
Related videos on Youtube
EJC
Willing to help as much as I can. I will get the job done.
Updated on September 17, 2022Comments
-
EJC over 1 year
Can anyone help me write a script to run all of the SQL files in a particular folder?
I need to deploy a bunch of Stored Procedures and I want to deploy them all at once without having to manually run each one. We run Microsoft SQL Server 2005.
-
EJC over 13 yearsNo order. So I can just use run C:\folder\*.sql how does it know what DB to run them on?
-
ChrisF over 13 years@EJC - you can specify the database in the script - I don't have an example immediately to hand.
-
EJC over 13 yearsI'm running Windows 7, I assume they didn't take away the For loop, but you never know. Do you know if this works on 7?
-
Alberto Martinez over 13 yearsThe Windows team of Microsoft take compatibility of batch files very seriuously, so they don't change almost anything from the batch interpreter or they'll break existing batch files (which are used a lot in some corporate environments), see blogs.msdn.com/b/oldnewthing/archive/2005/09/09/462906.aspx.
-
ufo over 5 yearsIt would be useful to know how to run scripts that are in the subfolders.