CODEWITHSUNDEEP
sqlsql-serverssms
Przemyslaw Remin
Przemyslaw Remin

Reputation: 6950

How to run all scripts in SSMS

Is it possible to run all opened scripts in SSMS. Would be any shortcut for that? Just like F5 runs only active one script. The reason I want it is a temporary change in all scripts with CTRL+H. I want to run all scripts without saving changes. That is why I do not want the idea of running all scripts in a directory.

Update. Nope, manually won't be faster. The reason why I need it is exactly here: VBA clear just pivot table cache, but leaving pivot table structure I have to run it on 6 scripts not just once, but anytime I make changes to either SQL code or Excel file. Manually is frustrating for that.

Upvotes: 1

Views: 7285

Answers (4)

juntunen
juntunen

Reputation: 217

Fastest way I've found is to open a folder containing your .sql files using SSMS with File > Open Folder ... (Ctrl+Shift+Alt+O) which will display them in the Solution Explorer in SSMS. Double click each file to open and use Ctrl + E to execute. Continue clicking down the list until you are done.

Upvotes: 1

Bacon Bits
Bacon Bits

Reputation: 32230

You can kind of do this with SQLCMD mode.

  1. Create a new Query. Make sure Query -- > SQLCMD Mode is enabled.
  2. In the new query, enter :r followed by the path to the scripts. So:

:r C:\Path\To\Script\1.sql
:r C:\Path\To\Script\2.sql
:r C:\Path\To\Script\3.sql
:r C:\Path\To\Script\4.sql
:r C:\Path\To\Script\5.sql
:r C:\Path\To\Script\6.sql

  1. Run your script. Each file should be executed in order.

You can't enable SQLCMD mode from within an SQL script, but you can get it to yell at you or stop executing if it's not enabled. You can also set SSMS to always start in SQLCMD mode in Tools --> Options --> Query Execution.

Upvotes: 4

miroxlav
miroxlav

Reputation: 12204

I agree with your drive for improvement. In this case, create stored procedure which executes all 6 scripts for you and takes argument (which you currently supply by replacing via Ctrl+H).

Then (another improvement) you can update your VBA code to connect to the SQL database and call that stored procedure for you. This way, you even won't need to open SSMS. Seamless and effective.

See also CREATE PROCEDURE, EXECUTE and code examples therein.

There is vast amount of resources on calling stored procedure from Excel.

Upvotes: 1

Polux2
Polux2

Reputation: 602

You can do this : Ctrl + (E F6 E F6 E ...)

Upvotes: 4

Related Questions