CODEWITHSUNDEEP
sql-serversql-server-2008functionstored-procedures
TTCG
TTCG

Reputation: 9113

Common function / stored procedures for all databases

We have a database server and it has about 10 databases.

I would like to create some functions / stored procedures which can be used in all databases.

For example, we can use sp_executesql in any database.

We have some requirements like that (getting current academic year, financial year, etc...)

Is it doable?

Upvotes: 1

Views: 4466

Answers (3)

Pondlife
Pondlife

Reputation: 16260

As others have suggested, you could put objects into the master database, but Microsoft explicitly recommends that you should not do that. I find that solution to be rather risky anyway, because the master database is 'owned' by the system, not by you, so there are no guarantees that it will continue to behave in the same way in the future.

Instead, I would consider this to be primarily a deployment issue. There are (at least) two strategies you could use:

  • Deploy the objects to every database
  • Deploy them to one 'reference' database that is only used for shared objects and create synonyms in the other databases

The second option is perhaps the better one, because if your functions use tables (e.g. you use a calendar table to get the academic year, which is much easier than calculating it) then you would have to create the same tables in every database too. By using synonyms, you only have to maintain one set of tables.

For the actual deployment, it's straightforward to use scripting to do manage the objects, because you just need a list of databases to connect to and run each DDL script against. You can do that using batch files and SQLCMD (perhaps with SQLCMD variables in your .sql scripts), or drive it from PowerShell or any other language that you prefer.

Upvotes: 4

Jon Egerton
Jon Egerton

Reputation: 41569

A couple of options:

You can use a system stored procedure as Cade says. I've done this in the past and it works ok. One warning on this is that the sp_MS_marksystemobject procedure is undocumented, which may mean that it could vanish or change without warning in future SQL versions. Thinking back I think there were other problems using this approach with functions though.

Another approach is to use standardized procedure and functions, and roll them out across your databases using sp_MSforeachdb to run code against every database. If you need to run against only your 10 database you can take copy the code in this procedure and modify it to check that a database matches your schema before running the code (or you can write your own version that does a similar thing).

Upvotes: 0

Cade Roux
Cade Roux

Reputation: 89721

Depending upon what the SP actually does, you want to create the procedure in master, name it with sp_ and mark it as a system procedure:

http://weblogs.sqlteam.com/mladenp/archive/2007/01/18/58287.aspx

Upvotes: 1

Related Questions