在服务器的所有数据库参考存储过程 [英] Reference Stored procedures for all databases in server

问题描述

我要创建一个和多个同一个存储过程（甚至全部）我的SQL Server 2008上的数据库。

I want to create one and the same stored procedure for multiple (even all) databases on my SQL Server 2008.

我能做到一次或者我应该在asp.net项目的参考数据库单独创建呢？

Can i do it at once or should I create it in asp.net project reference database separately?

请帮我.....

推荐答案

这个问题已经被问多次已经在SO，但有趣的是似乎有没有关于最好的答案一致。因此，这里是各种人建议的主要选项汇总（在没有特殊的顺序排列）：

This question has been asked multiple times already on SO, but interestingly there seems to be no consensus about the best answer. So here is a summary of the main options various people have suggested (in no special order):

1. 与把它放在主数据库SP _ preFIX以便SQL Server查找它有第一个


2. 把它放在模型数据库，所以它会自动添加到所有新的DB


3. 仅适用于全球性的程序（和其他对象）创建一个数据库，并使用三部分命名称呼他们


4. 由于3，但在创建其他数据库同义词，所以你并不需要三部分命名


5. 使用商用或自行开发的工具来管理部署到多个数据库


6. 5，但部署到一个数据库中，那么差异数据库和应用的差异脚本部署到其他数据库

1. Put it in the master database with an sp_ prefix so that SQL Server looks for it there first
2. Put it in the model database, so it is automatically added to all new DBs
3. Create a database only for 'global' procedures (and other objects) and call them using three-part naming
4. As 3, but create synonyms in other databases so you don't need the three-part naming
5. Use commercial or self-developed tools to manage deployment to multiple databases
6. As 5, but deploy to one database, then diff the databases and apply the diff script to deploy to other databases

在我看来，1是一个非首发，因为微软明确说你不应该建立在master数据库中的对象。 2听起来不错，但在实践中的数据库恢复或复制往往比从头开始（因人而异）创建的，因此使用模式是靠不住的。

In my opinion, 1 is a non-starter because Microsoft explicitly says you should not create objects in the master database. 2 sounds good but in practice databases are restored or copied more often than created from scratch (YMMV), so using model is unreliable.

3和4是良好表和意见，但执行上下文可以存储过程和函数是一个问题。但是，这取决于程序的逻辑，这可能是你的情况是可行的。

3 and 4 are good for tables and views, but execution context can be a problem for stored procedures and functions. But this depends on the logic in the procedures and it may be workable in your case.

但1-4都具有潜在的问题，如果你只有一个对象，你只能有对象的一个​​版本，它常常是有用，可以让不同的DB提供不同的版本，用于测试或只是针对不同的客户。

But 1-4 all have the potential problem that if you only have one object, you only have one version of that object, and it's often useful to have different versions available in different DBs, for testing or just for different customers.

5和6是同一主题的变化，我个人觉得这是最好的办法，因为部署是你无论如何都要解决一个问题，所以你还不如这样做，并确保你有足够的知识，工具和代替进程能够干净地和快速部署code键以受控，自动化的方式的任何数据库。

5 and 6 are variations on the same theme and I personally find this is the best approach because deployment is a problem you have to solve anyway, so you might as well do it and make sure that you have the knowledge, tools and processes in place to be able to deploy code cleanly and quickly to any database in a controlled, automated way.

这篇关于在服务器的所有数据库参考存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！