CODEWITHSUNDEEP
mysqlsqlstored-proceduresmultiple-databases
Prashanth Kumar B
Prashanth Kumar B

Reputation: 576

updating Stored Procedure for multiple database

I have 80 different databases to maintain and all the databases has the same tables and stored procedures but the information stored is different, the way it works is that we have created a separate database for each client, and each client will at-least have some 2000 - 3000 rows of data to work with in some selected tables.

My first question is - now I have to change the stored procedure for all 80 databases. Normally I would have to go to each database and update the stored procedure which is a hideous task, but is there some kind of technique where I update a single stored procedure for one database and all the stored procedures for other 80 databases will get updated automatically.

My second question is - is it ideal to use multiple databases or create a single database and identify a client using his client id.

Any thoughts would be amazingly helpful,

Thank you, Prashanth Kumar B

Upvotes: 0

Views: 645

Answers (1)

Dave Evans
Dave Evans

Reputation: 26

If you have the list of servers and databases in Excel, you could:

1) Script the stored procedure to a file (say C:\myproc.sql)

2) Use Excel to create a separate osql command for each server/database combination like this:

ServerName  DatabaseName   SQLCommand
==========  ============   ==========
SERVER1     DATABASE1      ="osql -E -i ""C:\myproc.sql"" -S " & A2 & " -d " & B2
SERVER1     DATABASE2      ="osql -E -i ""C:\myproc.sql"" -S " & A3 & " -d " & B3
SERVER2     DATABASE1      ="osql -E -i ""C:\myproc.sql"" -S " & A4 & " -d " & B4
SERVER2     DATABASE2      ="osql -E -i ""C:\myproc.sql"" -S " & A5 & " -d " & B5

3) Copy the generated SQL commands into a batch file/Powershell script

4) Run the batch file/Powershell script at a Command Prompt.

As to your second question, I would say it would definitely be better to have 1 database and all the clients in that database (as long as your clients are happy with this arrangement), as your issue is just going to get worse and worse as you have more clients. Also you are going to have an overhead of backing up all the different database whereas you could just do it once if you have the one database.

Upvotes: 1

Related Questions