Add SPROC to each database in SQL server 2000 1

[clanm]

Hello!

I have about 15 databases on our instance of SQL Server, a and it's only growing. I'm trying to figure out how to run a stored procedure for each database without having to run it for the first in Query Analyzer, then select from the dropdown list at the top the next db, click "Execute", then select the next name....and so on. Seems like there's gotta be a better way. I'm searching Books Online, and haven't found anything yet.

Thanks!

 

[jrbarnett]

See

http://www.databasejournal.com/features/mssql/article.php/34

41031

John

 

[clanm]

jrbarnett,

Thanks for the link.

Found out about sp_MSforeachdb.

Was wondering about just putting the SPROCs in the the master db......but I label mine not as "sp_...." but as "usp_..." If the SPROC is in master, can any database then use the SPROCS in there....I'm thinking yes.

Thanks!

 

[jrbarnett]

You can put them in master, but you would need to prefix it with the database name, for example:

EXEC master..usp_myproc

The sp_ prefix tells SQL Server to look in master ahead of the current database, which is why user SP's shouldn't use it.
I'd strongly advise against putting them in master as this stores the SQL Server global system configuration.
If you must create shared objects of any type, create a database to store your server wide objects, set up a database role for accounts that will need to run it and grant appropriate permissions to the role and make the accounts members of this role.
Then, call them with something like:

EXEC sharedobjs..usp_dosomething

John

 

[clanm]

Okay...cool..thanks for the tip.

So, create a db and grant permissions for other dbs to access.

I'd do:
1. Make new db "mynewdb"
2. Make new SPROC "usp_mynewSPROC"
3. To run new SPROC: EXEC mynewdb..usp_mynewSPROC

Dumb question, but is the ".." for a default owner?

Thanks again!

 

[jrbarnett]

Yes, the two dots is for the default owner (dbo).

You will need to change the string to databasename.ownername.usp_spname if you have a non dbo owner, but this introduces other issues into the equation.

John