CODEWITHSUNDEEP
sqlsql-server-2008
Carlos Blanco
Carlos Blanco

Reputation: 8762

Way of executing several .sql files at once in SQL Server

I have around 100 .sql files that create stored procs. I'd like to execute them at once all of them.

They are contained inside a folder called Stored Procedures. There several other folders with different names --Table names-- inside the Stored Procedures folder.

Is there a way to execute all these files at once?

Upvotes: 3

Views: 2085

Answers (3)

Scott Saad
Scott Saad

Reputation: 18362

You could open up a PowerShell instance and run a command like the following:

Get-ChildItem ".\Stored Procedures\*.sql" | ForEach-Object { sqlcmd -S ServerName -d DatabaseName -E -i $_.FullName }

This will get each .sql file in the Stored Procedures directory, and one by one, pass it to the sqlcmd.exe program. To get more details on the parameters for sqlcmd you can run sqlcmd /?.

To make it recursive through sub directories, you can apply the -Recurse parameter to Get-ChildItem along with the -Filter parameter:

Get-ChildItem '.\Stored Procedures' -Filter *.sql -Recurse | ForEach-Object { sqlcmd -S ServerName -d DatabaseName -E -i $_.FullName }

Upvotes: 3

Pankaj
Pankaj

Reputation: 10095

Here is the another tool Sql_Server_Script_Executor

Is there a way to execute all these files at once?

*You can add folder and all your files will comes up in the list. Click the execute button and done!!!!!!!!!!!!! *

It contains three transaction modes.

1. Execute scripts within one transaction
2. Execute scripts on separate transaction
3. No transaction

and many more features please go through it.

enter image description here

Upvotes: 2

marc_s
marc_s

Reputation: 754250

Not at once - but all in one go - try a tool like the SSW Deploy one.

enter image description here

Upvotes: 1

Related Questions