To create a batch file to run all the sql scripts

[aa_dev]

Hi,

I need to create a batch file with the scripts which is in .sql format.
table_schema.sql
stored procedures.sql
uddtypes.sql
uddtables.sql
grant permission.sql
sql injection.sql
users.sql

These files hold all scripts for creation of schema, stored procedures, users, uddt's etc
I need to create a single batch file so that when i run it, automatically the schema with stored procedures and everything should be created in my mssql 2008.

create table..... (execute 'table_schema')
create table.....(execute 'stored procedures')
....
...

it should be given like this
can u give me the script?

[abc0502]

I don't know about MySQL, but if all what you need to do is just calling the files and the assigned program will run for the .sql extension, this should work.

Code: Select all

@Echo Off & Cls & Mode 60,12

Echo Create Table ..... [ execute 'table_schema' ]
"C:\path\to\table_Schema.sql"

Echo Create Table ..... [ execute 'stored procedures' ]
"C:\path\to\stored procedures.sql"

and add the rest of sql files in this way.

Note:
"C:\path\to\" is the full path of the .sql files, and if the sql files in the same folder with the batch, you don't need to put the full location, just the name of the file with the extension.

and if it didn't work, then open the files with the SQL program like this.

Code: Select all

"C:\path\to\MySQL_program.exe" "C:\path\to\stored procedures.sql"

here you will need the full path to the sql files

and some times the exe programs has switches, you should search for it, it might help.

[aa_dev]

Thanks a lot...
I'm using SQL server 2008 and not mysql
Can i have this as
create database dbname
(calling all the .sql scripts one by one for creation here)
....
...

so that when i deploy the script it automatically creates the whole database with its schema, stored procedures, uddtypes etc...
Also where should i specify the -S servername, -U username -P password in the script?

[phillid]

You would need to specify those in the command that calls your SQL client... I'm not familar with MSSQL, but I am familar with MySQL.. I can be of little help here.

Try issuing 'C:\path\to\mssqlclient.exe /?' or 'C:\path\to\mssqlclient.exe /help' or 'C:\path\to\mssqlclient.exe --help' or something along those lines. That will give you the means of specifying your username and password. In the batch script, it'd just be a matter of doing something like:

Code: Select all

"C:\path\to\MySQL_program.exe" -U username -P password "C:\path\to\stored procedures.sql"

Again, adjust this depending on whatever the help dishes out..

Phillid

[abc0502]

As Phillid said about intering the username and passwords switches, these are the main program switches that use the .sql files to create the database you need.

but to automatically run scripts one by one, you first has to sort them in order like changing there names by adding numbers at the first like 1-script_nam.sql

and with this batch it will sort the files and then run them one by one,
(but test it first on a copy just in case any thing goes wrong)

Code: Select all

@Echo off & cls

:: example: C:\sql_folder
set "sql_files=location to the folder where the .sql files exist like example above"

For /F "tokens=* delims=" %%A in ('Dir /B /O:N /A:-D "*.sql"') Do (
    "C:\location\to\SQL\program.exe" -S servername, -U username -P password "%%A"
)

this should run the main application that use the .sql files and pass the full location to the sql files one by one in the order you set by renaming it.

Note that it will pass the switches on every .sql file and folder that contain .sql files must NOT have any other sql files you don't need to use