运行目录中的所有 SQL 文件

Run all SQL files in a directory

提问by K.A.D.

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:

我有许多 .sql 文件，我必须运行这些文件才能应用其他开发人员对 SQL Server 2005 数据库所做的更改。这些文件根据以下模式命名：

0001 - abc.sql
0002 - abcef.sql
0003 - abc.sql
...

Is there a way to run all of them in one go?

有没有办法一次运行所有这些？

回答by Vijeth

Create a .BAT file with the following command:

使用以下命令创建 .BAT 文件：

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

请注意，提供用户/密码时不需要“-E”

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!

将此 .BAT 文件放在要执行 .SQL 文件的目录中，双击 .BAT 文件即可完成！

回答by Remus Rusanu

Use FOR. From the command prompt:

使用FOR。从命令提示符：

c:\>for %f in (*.sql) do sqlcmd /S <servername> /d <dbname> /E /i "%f"

回答by Ramakrishna Talla

1. 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
   

2. Go to Query menu on SQL Management Studio and make sure SQLCMD Mode is enabled

3. 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
   

4. Now execute

1. 在 SQL Management Studio 中打开一个新查询并键入所有文件，如下所示

   :r c:\Scripts\script1.sql
   :r c:\Scripts\script2.sql
   :r c:\Scripts\script3.sql
   

2. 转到 SQL Management Studio 上的查询菜单并确保启用 SQLCMD 模式

3. 点击SQLCMD模式；文件将被选择为灰色，如下所示

   :r c:\Scripts\script1.sql
   :r c:\Scripts\script2.sql
   :r c:\Scripts\script3.sql
   

4. 现在执行

回答by Ashish Gupta

Make sure you have SQLCMD enabled by clicking on the Query > SQLCMD mode option in the management studio.

通过单击管理工作室中的查询 > SQLCMD 模式选项，确保您已启用 SQLCMD。

1. Suppose you have four .sql files (script1.sql,script2.sql,script3.sql,script4.sql) in a folder c:\scripts.

2. 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.

3. Create a batch file named ExecuteScripts.batwith the following:

   SQLCMD -E -d<YourDatabaseName> -ic:\Scripts\Main.sql
   PAUSE
   

   Remember to replace <YourDatabaseName>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
   

4. Execute the batch file by double clicking the same.

1. 假设您script1.sql,script2.sql,script3.sql,script4.sql在一个文件夹中有四个 .sql 文件 ( ) c:\scripts。

2. 使用以下内容创建主脚本文件 (Main.sql)：

   :r c:\Scripts\script1.sql
   :r c:\Scripts\script2.sql
   :r c:\Scripts\script3.sql
   :r c:\Scripts\script4.sql
   

   将 Main.sql 保存在 c:\scripts 本身中。

3. 创建一个批处理文件，命名ExecuteScripts.bat如下：

   SQLCMD -E -d<YourDatabaseName> -ic:\Scripts\Main.sql
   PAUSE
   

   请记住<YourDatabaseName>用要执行脚本的数据库替换。例如，如果数据库是“Employee”，则命令如下：

   SQLCMD -E -dEmployee -ic:\Scripts\Main.sql
   PAUSE
   

4. 通过双击执行批处理文件。

回答by Kevin L

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):

您可以使用ApexSQL 传播。它是一个免费工具，可以在多个数据库上执行多个脚本。您可以根据需要选择任意数量的脚本，并针对一个或多个数据库（甚至多个服务器）执行它们。您可以创建脚本列表并保存它，然后在每次您想按照创建的顺序执行相同脚本时选择该列表（也可以添加多个脚本列表）：

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:

选择脚本和数据库后，它们将显示在主窗口中，您只需单击“执行”按钮，所有脚本将按给定的顺序在选定的数据库上执行：

回答by Lijo

General Query

一般查询

save the below lines in notepad with name batch.batand place inside the folder where all your script file are there

将以下几行保存在记事本中，名称为batch.bat并放在所有脚本文件所在的文件夹中

 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

对于 (*.sql) 中的 %%G 执行 sqlcmd /S NFGDDD23432/d EMPLYEEDB-i"%%G" pause

sometime if login failed for you please use the below code with usernameand 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

对于 (*.sql) 中的 %%G 执行 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

在脚本文件所在的文件夹中创建 bat 文件后，只需单击 bat 文件即可执行脚本

回答by Clinton Ward

I wrote an open source utilityin C# that allows you to drag and drop many SQL files and start running them against a database.

我用 C#编写了一个开源实用程序，它允许您拖放许多 SQL 文件并开始针对数据库运行它们。

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 & tested with SQL 2008
• Single exe file
• Kill connection at anytime

• 拖放脚本文件
• 运行脚本文件目录
• Sql脚本在执行期间输出消息
• 脚本通过或失败，颜色为绿色和红色（黄色表示运行）
• 停止错误选项
• 在错误选项上打开脚本
• 运行报告，每个脚本花费的时间
• 总持续时间
• 测试数据库连接
• 异步
• .Net 4 & 用 SQL 2008 测试
• 单个exe文件
• 随时终止连接

回答by Aseem Gautam

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.

据我所知，您可以使用 osql 或 sqlcmd 命令来执行多个 sql 文件。缺点是您必须为这两个命令创建一个脚本。

Using SQLCMD to Execute Multiple SQL Server Scripts

使用 SQLCMD 执行多个 SQL Server 脚本

OSQL (This is for sql server 2000)

OSQL（这是用于 sql server 2000）

http://msdn.microsoft.com/en-us/library/aa213087(v=SQL.80).aspx

http://msdn.microsoft.com/en-us/library/aa213087(v=SQL.80).aspx

回答by PepiX

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

我发现的最简单的方法包括以下步骤（唯一的要求是它必须在 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

• 在资源管理器中打开文件夹
• 选择所有脚本文件
• 按 Shift
• 右键单击选择并选择“复制为路径”
• 转到 SQL Server 管理工作室
• 创建新查询
• 查询菜单，“SQLCMD 模式”
• 粘贴列表，然后按 Ctrl+H，将 '"C:\'（或任何驱动器号）替换为 ':r "C:'（即在行前加上 ':r '）
• 运行查询

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

听起来很长，但实际上非常快..（即使是最小的步骤，听起来也很长）

回答by Nagaraju Chimmani

@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 < %%a
)

Step1: above lines copy into note pad save it as bat.

Step1：将以上几行复制到记事本中另存为bat。

step2: In d drive abc folder in all Sql files in queries executed in sql server.

step2：在d盘abc文件夹中所有sql文件中sql server执行的查询。

step3: Give your ip, user id and password.

step3：提供你的ip、用户名和密码。