Web Development

Ask something
Discussion Locked

Update Multiple SQL Server 2000 DBs

0 helpful
7 replies

by bgibilaro

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am working on a program that creates a new database for each account that is created within the program. The tables are always going to structured identically. We are using MS SQL Server 2000

My questions is how can I institute a change that will effect every database on that server at the same time?

For example:

I suddenly want to add an email column in the users table. Well, obviously I have no intention of manually updating each and every db so that they all have an email column in the users table. I also do not want to simply enter the SQL into query analyzer, and one at a time, update each db.

How can I do this is one shot, no matter how many dbs there are or what changes I want made? Any help in this matter would be greatly appreciated.

Bob Gibilaro
Network Administrator
V. Alexander & Co., Inc.

7 replies

by ckmellin

This response really doesn't answer your question, but, why in world would you want to create multiple database with identical structures? Couldn't you simply use a single database for all users. If you need to track thier activities separatly, this can easily be accomplished in a single db. Maybe you should rethink your system design.

by bgibilaro

Actually, there is going to be upwards of 30 tables or so. This is going to be a pretty extensive app. The reason for the multiple databases is because user accounts, and more importantly, the amount they pay every month is based on their space usage, so I need a way to reliable know how much space they are using.

On the other hand, if anyone can tell me how I might query for every row related to that user, and then get a size total in MB, that would be even mopre useful.

For a very simple example, lets say I query for all records in Table A with a ContactID of 1. I would then want to know, how much space that set of data is taking up.

I hope this clarifies a bit. Thanks for the help.

by p7ants

Sounds like a stored procedure that uses for input a table that you update with database names as they are created and the name of the column to be added.

by jmiller1

You could set the server up for replication as both a Publisher and Subscriber, and replicate the data from one database to the next. That way you wouldn't have to code anything. Just update the 1 database, and let SQL Server push the data to eachof the other databases.

This discussion is closed to new replies.