Run all SQL files in a directory

Question

I have a number of  sql files which I have to run in order to apply changes made by other developers on an SQL Server 2005 database  The files are named according to the following pattern   0001 - abc sql 0002 - abcef sql 0003 - abc sql       Is there a way to run all of them in one go

User · Answer

The easiest way I found included the following steps (the only requirement is it to be in Win7+):

open the folder in Explorer
select all script files
press Shift
right click the selection and select "Copy as path"
go to SQL Server Management Studio
create a new query
Query Menu, "SQLCMD mode"
paste the list, then Ctrl+H, replace '"C:\' (or whatever the drive letter) with ':r "C:' (i.e. prefix the lines with ':r ')
run the query

It sounds long, but in reality is very fast.. (it sounds long as I described even the smallest steps)

User · Answer

In the SQL Management Studio open a new query and type all files as below   r c  Scripts script1 sql  r c  Scripts script2 sql  r c  Scripts script3 sql  Go to Query menu on SQL Management Studio and make sure SQLCMD Mode is enabled Click on SQLCMD Mode  files will be selected in grey as below   r c  Scripts script1 sql  r c  Scripts script2 sql  r c  Scripts script3 sql  Now execute

User · Answer

Use FOR  From the command prompt   c   gt for  f in    sql  do sqlcmd  S  lt servername gt   d  lt dbname gt   E  i   f

User · Answer

If you can use Interactive SQL   1 - Create a  BAT file with this code    ECHO OFF ECHO for   G in    sql  do dbisql -c  uid dba pwd XXXXXXXX ServerName INSERT-DB-NAME-HERE    G pause   2 - Change the pwd and ServerName   3 - Put the  BAT file in the folder that contains  SQL files and run it

User · Answer

echo off cd C  Program Files  x86  MySQL MySQL Workbench 6 0 CE  for   a in  D  abc   sql  do   echo   a mysql --host ip --port 3306 --user uid--password ped  lt    a     Step1  above lines copy into note pad save it as bat   step2  In d drive abc folder in all Sql files in queries executed in sql server   step3  Give your ip  user id and password

User · Answer

Make sure you have SQLCMD enabled by clicking on the Query  gt  SQLCMD mode option in the management studio    Suppose you have four  sql files  script1 sql script2 sql script3 sql script4 sql  in a folder c  scripts  Create a main script file  Main sql  with the following    r c  Scripts script1 sql  r c  Scripts script2 sql  r c  Scripts script3 sql  r c  Scripts script4 sql   Save the Main sql in  c  scripts itself  Create a batch file named ExecuteScripts bat with the following   SQLCMD -E -d lt YourDatabaseName gt  -ic  Scripts Main sql PAUSE   Remember to replace  lt YourDatabaseName gt  with the database you want to execute your scripts  For example  if the database is  Employee   the command would be the following   SQLCMD -E -dEmployee -ic  Scripts Main sql PAUSE  Execute the batch file by double clicking the same

User · Answer

I wrote an open source utility in C  that allows you to drag and drop many SQL files and start running them against a database   The utility has the following features    Drag And Drop script files   Run a directory of script files   Sql Script out put messages during execution   Script passed or failed that are colored green and red  yellow for running    Stop on error option   Open script on error option   Run report with time taken for each script   Total duration time   Test DB connection   Asynchronus    Net 4  amp  tested with SQL 2008   Single exe file   Kill connection at anytime

User · Answer

Create a  BAT file with the following command   for   G in    sql  do sqlcmd  S servername  d databaseName -E -i   G  pause   If you need to provide username and passsword   for   G in    sql  do sqlcmd  S servername  d databaseName -U username -P  password -i   G    Note that the  -E  is not needed when user password is provided  Place this  BAT file in the directory from which you want the  SQL files to be executed  double click the  BAT file and you are done

User · Answer

You could use ApexSQL Propagate  It is a free tool which executes multiple scripts on multiple databases  You can select as many scripts as you need and execute them against one or multiple databases  even multiple servers   You can create scripts list and save it  then just select that list each time you want to execute those same scripts in the created order  multiple script lists can be added also      When scripts and databases are selected  they will be shown in the main window and all you have to do is to click the    Execute    button and all scripts will be executed on selected databases in the given order

User · Answer

What I know you can use the osql or sqlcmd commands to execute multiple sql files  The drawback is that you will have to create a script for both the commands    Using SQLCMD to Execute Multiple SQL Server Scripts  OSQL  This is for sql server 2000   http   msdn microsoft com en-us library aa213087 v SQL 80  aspx

User · Answer

For executing every SQLfile on the same directory use the following command   ls   awk   print     0    gt  all sql   This command will create a single SQL file with the names of every SQL file in the directory appended by       After the all sql is created simply execute all sql with SQLPlus  this will execute every sql file in the all sql

User · Answer

General Query  save the below lines in notepad with name batch bat  and place inside the       folder where all your script file are there   for   G in    sql  do sqlcmd  S servername  d databasename  -i   G      pause   EXAMPLE  for   G in    sql  do sqlcmd  S NFGDDD23432  d  EMPLYEEDB  -i   G  pause  sometime if login failed for you please use the below code with username and password  for   G in    sql  do sqlcmd  S SERVERNAME  d DBNAME -U USERNAME -P PASSWORD -i   G  pause   for   G in    sql  do sqlcmd  S  NE8148server   d  EMPLYEEDB  -U Scott  -P tiger -i   G  pause  After you create the bat file inside the folder in which your Script files are there just click on the bat file your scripts will get executed

User · Answer

You can create a single script that calls all the others   Put the following into a batch file    echo off echo  gt    dp0all sql  for   i in     dp0   sql  do echo      fi   gt  gt     dp0all sql    When you run that batch file it will create a new script named all sql in the same directory where the batch file is located  It will look for all files with the extension  sql in the same directory where the batch file is located   You can then run all scripts by using sqlplus user pwd  all sql  or extend the batch file to call sqlplus after creating the all sql script

[sql] Run all SQL files in a directory

Examples related to sql

Examples related to sql-server

Examples related to batch-processing