Running same SPs/UDFs on different databases

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Running same SPs/UDFs on different databases

Author

Topic

thusi
Starting Member

25 Posts

Posted - 2008-12-03 : 17:14:56

Hi All
At the moment I have only a single database (let's call it DB1) and have several UDFs/SPs I have created. I created these using SQL Server Management Studio and have them all under Databases->DbName -> Stored Procedures or Functions. My problem is now we are going to have another dataset which will be imported into a new database (DB2), but I don't want to simply create all the SPs/UDFs from DB1 again in DB2 ending up with duplicates. Both databases will have the same tables, and I simply want a scheme where I can easily specify the database and the queries should run against it. Is there something like global SPs/UDFs in SQL Server?
Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-03 : 17:49:33

So you mean DB2 already exists with some SP/UDF that are in DB1?

thusi
Starting Member

25 Posts

Posted - 2008-12-03 : 18:02:14

I mean...DB1 already exists with all tables, SPs/UDFs etc. Now I'm going to add a new db - DB2, which will have all the same tables as DB1 with same table structure etc, and I want to use the same SPs/UDFs I have in DB1. So if there's this magical 'global SPs/UDFs' type of place holder in SQL Server, I'd want to have all my SPs/UDFs there (instead of DB1) and simply pass the name of the database ('DB1', 'DB2' for eg) which will execute the query on the selected DB.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-03 : 21:10:50

There is no such magic in SQL Server.

thusi
Starting Member

25 Posts

Posted - 2008-12-03 : 21:24:10

hmm..seems like doing something like:

input @dbName
select *
from @dbName + 'dbo.myFunc(' + '' + 'some str' + '' + ')'

is one way of doing it? I have to pass quite a few strings..so creating the sql with so many quotes is going to be crazy!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-03 : 21:37:45

Do you mean this?

select (query)..... from DBNAME.SCHEMA.TABLENAME/VIEW
or
EXEC DBNAME.SCHEMA.PROCNAME

thusi
Starting Member

25 Posts

Posted - 2008-12-03 : 22:29:18

Well..think I'm going to end up using scripting - http://www.eggheadcafe.com/software/aspnet/29255866/copy-database-with-only-s.aspx where you simply create a copy of the database structure. Not the ideal solution, cos it'll possibly be a pain to maintain later on..but should be ok I think. Thank for your help sodeep.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-04 : 09:46:08

Any Reason to Copy database?

thusi
Starting Member

25 Posts

Posted - 2008-12-04 : 15:05:03

Well, we do fresh data extracts from general practices for research work, and don't want to have all the extracts in the same DB. In a production type environment I suppose you'd have a foreign key to a "PracticeLocation" type table, but our data is not normalised..so having multiple DBs at the moment. Is that what you meant?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources