Is there a standard way to use common data and procedures across multiple databases?

We have several independent databases that have data and code in common, not in the sense that it is accessed between the databases but in the sense that the data means the same thing in each database, and the code does the same thing.

Examples are:

• Configuration settings (e.g. error codes 50xxx, boilerplate text for company name, etc).

• Procedures and functions that perform common tasks (e.g. converting a CSV string into a table, logging an error, formatting an error message based on the error code).

• Table structures (e.g. table for database version history, table for logging errors). As well as the columns, constraints, and triggers there are also common procedures and functions that read/write the data.

• Look-up tables (e.g. date look-up table containing dates between 2000-2100). These are similar to table structures but quite often the data will be the same across the databases. In the case of the date table, the start and end dates are configuration settings, which are read by a function, then the date data is generated by a procedure. All these operations are common between the databases.

• User defined types (e.g. types for passing tables to functions).

For maintenance and support reasons I think it makes sense for things like error codes, procedures, functions, and types to have a "single point of truth" across all the databases, not a different truth in each database.

At the moment each database has it's own copy of everything, including source repository, and we maintain them all independently. This is far from ideal because it's too easy to fix a procedure in A and forget to put it in B, or add an error code to A and the same one to B but they mean different things, etc.

The databases are not updated at the same time and they don't necessarily reside on the same hardware. There are cases where they can read data from each other (if the other one exists).

Is there a standard way of having a single point of truth for data/code that is used a

In addition to Thomas' answer, for the things like common lookup data and generic functions, you can use synonyms.

USE CentralDB;
GO

CREATE TABLE dbo.Dates(...);
GO

USE OtherDB;
GO

CREATE SYNONYM dbo.Dates
  FOR CentralDB.dbo.Dates;

Now your other databases can treat these like first-class, local objects, but you only need to maintain one copy. Note that some functionality is missing, e.g. you can't set up foreign keys.

In a previous life I managed a system where we would have ~500 databases with nearly identical schema on each instance. We managed common data using synonyms, and deployment for code that had to be local using Red-Gate SQL Compare an SQL Farms Combine (very much like Multi-Script).

StackExchange Database Administrators Q#42561, answer score: 6