>>>>Hi All,
>>>>
>>>>I currently have about 10 databases. One of the databases stores information about all the other databases. I want to write stored procedures that all dtabases will access without having to manage each procedure from within each database, since each will all be the same, respectively.
>>>>
>>>>Is it acceptable to store all my procedures in one database where all my other databases will call the procedures?
>>>>
>>>>Are there performance implications?
>>>>
>>>>Thanks in advance....
>>>>Michael
>>>
>>>Michael,
>>>While it is acceptable, I don't see how it will be beneficial to have them stored singularly except for maintenance purposes. I could be mistaken depending on what T-SQL code you have in your SPs.
>>>
>>>If you are processing row sets for individual databases and tables, I see a big performance hit in centralizing your SPs. If you centalize then you would need to use Dynamic SQL in order to process databases and tables specified by the user. The SQL statements would have to compiled and optimized whenever the SP was run thereby short circuiting one of the reasons to use SPs. In order to be pre-optimized, you would need to hard code the database and table names in the SP at design time.
>>>
>>>My $0.02.
>>
>>
>>Thanks for your response....
>>
>>Actually, all the databases I speak of are identical. They are mirror images of each other. Each one supports a different web site, but each one is created equal.
>>
>>That is what made me propose the question....
>>
>>So I could in fact hardcode the table names, but I would not be able to hardcode the database names. So, as such I would assume I would agian lose the benefit of the SP????
>>
>>Thanks again
>
>Michael,
>
>Since you are cloning databases, why don't you create your stored procedures in the MODEL database? Then when you create the next clone the stored proc are automatically added from the MODEL. In fact, if everything is "always" the same make the MODEL a clone. Then all you have to do is issue a CREATE DATABASE statement.

Excellent.... Thanks...