CODEWITHSUNDEEP
sql-serverstored-proceduresssms
Ludovic Wagner
Ludovic Wagner

Reputation: 125

SSMS / SQLCMD batch to create all stored procedures within a sub-folder

I developped the following T-SQL script with a bit of SQLCMD to call separate stored procedure script for their creations. The stored procedures are located in a child Store Procedure\ folder (I want to keep the space).

They are probably better ways to perform it, but I wan to a unique script (but using files) which creates the database, the tables, the stored procedures and so on.

I detail neither the the database, nor the table scripting.

/* Create database (I still have some issues with dropping it (existing connections) */

*/ Create tables with constraints... */

-- Is it possible to declare a relative path (here MY_PARENT_PATH is replaced by "c:\...")
:setvar STORE_PROCEUDRE_PATH "[MY_PARENT_PATH]\Stored Procedures"

-- Calls CRUD stored procedures from separate scripts via SQLCMD - is it possible to batch it with all spXXX_XXXX.sql files included in the sub-folder?
:r $(STORE_PROCEUDRE_PATH)\spFields_Create.sql
:r $(STORE_PROCEUDRE_PATH)\spFields_Read.sql
:r $(STORE_PROCEUDRE_PATH)\spFields_Update.sql
:r $(STORE_PROCEUDRE_PATH)\spFields_Delete.sql

-- Idem for other table, such as Features, Values, etc.

Is it possible to loop all files spXXX_XXXX.sql files within the Store Procedure\ folder and execute the :r $(STORE_PROCEUDRE_PATH)\spXXX_XXXX.sql scripts?

I came accross several articles which show example using a FOR construct, but I got confused.

Thanks for any insights :-)

Upvotes: 0

Views: 1102

Answers (1)

eshirvana
eshirvana

Reputation: 24568

yes there is a way , SQLCMD is not able to loop through files but you can use Windows Batch Scripting to accomplish that.

here is a good blog about how to do it : SQLCMD and Batch File magic

so basically you need to make a .bat file ex: CRUDE.bat and edit and paste below code in it and save it ,

@@echo off
cd "[MY_PARENT_PATH]\Stored Procedures"

FOR %%A IN (*.SQL) DO ( sqlcmd -S [SERVERNAME] -d [DATABASE] -U [][username -P password] -i "%%A")

you need to replace these values with your values :

MY_PARENT_PATH : Your path or Directory where your suborder is.

SERVERNAME : Your Database Server Name.

DATABASE1 : Your Database name.

Username : Your SQL Username.

Password : Your SQL Password.

now you can run the batch file and it does the magic.

also here you can find out more about SQLCMD utility

Upvotes: 1

Related Questions