Mutiple Databases & one set of Stored Procs

[Meleagant]

All,

I have 15+ databases that are all the same as far as table structures.

I was thinking it would be nice to have one set of stored procs that could execute against any one of these databases instead of having 15+ copies of each and every SP or function.

Is this even possible? Can someone point me in the right direction?

* Sine scientia ars nihil est
* Respondeat superior

 

[RiverGuy]

If you already go through the hassle of deploying table schema changes to fifteen databases, why not add stored procedure deployments as well? I'd hate to use dynamic sql in every stored procedure just to save the little bit of time a single deployment provides.

 

[Meleagant]

I was trying to think of a way to do this without dynamic Sql as well. But it would be nice to have just one copy of the SPs. To shed a little more light on my plight...

I currently have these Dbs in Oracle. Prior to my arrival all of the Sql for the web apps are all inline Sql statements in the code. They don't use Oracle SPs as they were afraid of the maintenance maintaining copies of all of the Sps.

Now the company wants to migrate to Sql Server and keep the one Db per client set up. I was trying to think of a way I could alleviate their maintenance fears by saying we have one copy of the SPs going forward.

I know how to do simple cross server queries and such but have never been presented with a problem like this before.

* Sine scientia ars nihil est
* Respondeat superior

 

[gmmastros]

I know what some companies do....

They have a DB for development and another for testing. They force developers to keep their stored procedures in a source control application (like source safe, etc...). Then, the DB periodically will drop all of the stored procedures from the dev database and recreate them based on what is in source control. The first time this happens, some developers will scream because they will lose some of their work, but they will eventually get in to the habit of using source control for everything.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom