Joining Databases

Joining Databases - Catalogs, Employees, Users

This is a guideline for sharing data between two databases. This can be usefull when you have mutliple onsite buinesses that share resources like clients, employees or products.

You will need to consult with us on what data to share, as overtime our databases change and add features; so a set list of tables will not be provided here for any given concept.

To share tables between two databases in SQL Server, you can create a synonym in one database that points to the table in the other database. A synonym acts as an alias for the table, allowing you to reference it without needing to qualify it with the database name.

Here’s how you can create a stored procedure to create a synonym for a specified table in database1 to be accessible in database2.

Step 1: Create the Stored Procedure

sqlCopy codeCREATE PROCEDURE CreateTableSynonym
    @SourceDatabase NVARCHAR(128),
    @TableName NVARCHAR(128),
    @SynonymDatabase NVARCHAR(128),
    @SynonymName NVARCHAR(128)
AS
BEGIN

DECLARE @SQL NVARCHAR(MAX);

    SET @SQL = 'CREATE SYNONYM ' + QUOTENAME(@SynonymDatabase) + '.' + QUOTENAME(@SynonymName) +
                ' FOR ' + QUOTENAME(@SourceDatabase) + '.' + 'dbo.' + QUOTENAME(@TableName) + ';';

EXEC sp_executesql @SQL;

END;

Step 2: Execute the Stored Procedure

You can call this stored procedure to create a synonym. For example, if you want to create a synonym for the Employees table in database1 and access it as Employees in database2, you would run:

sqlCopy codeEXEC CreateTableSynonym 
    @SourceDatabase = 'database1', 
    @TableName = 'Employees', 
    @SynonymDatabase = 'database2', 
    @SynonymName = 'Employees';