How to create an alias of database in SQL Server

Question

We have a very old software has been created around 10 years ago and we don't have source code.

The software uses two databases, DB01 and DB02 on the same SQL Server 2012 instance.

There is SQL statements such as db01..table1 join db02..table2, but the main issue is our processes don't allow us use db02 as a name of database.

The question is: how we can create an alias of for database?

I was trying to use CREATE SYNONYM

CREATE SYNONYM [db02] FOR [db02_new_name];

but it doesn't work for database names.

Please suggest how it can be solved without patching a binary files to correct SQL statements.

Answer 1

Create a database with the name you want to impersonate. Re-jigg the DDL code generator to create a view for every table in the database that has the tables I need to access via the hardcoded name. Basically, each view will have a statement that looks like this..

CREATE VIEW schemaname.tablename as SELECT * FROM targetdbname.schemaname.tablename

Example:

The target database name that is hardcoded is called ProdDBV1 and the Source DB you have is named ProductDatabaseDatabaseV1, schema is dbo and table name is customer

1. Create the database called ProdDBV1 using SSMS or script.
2. CREATE VIEW dbo.customer as SELECT * FROM ProductDatabaseDatabaseV1.dbo.customer

If you can enumerate each table in your "source" database and then create the DDL as above. If you want I can update this posting with a code example. (using the sp_msforeachtable procedure if possible)

Answer 2

I had a similar issue.
Solved with this workaround, using synonyms.

Short version: You flood your database with a synonym of every object you'll ever need to reference. Later you re-create every synonym with the other database name.

Answer 3

Here's a stored proc to do it. Simply add it to your database and call it with the target database. It will create synonyms for all tables in the target database, and create the schemas if they don't exist. I've left a commented out section in case someone knows of a way to get the create schemas working without a cursor.

CREATE PROCEDURE CreateSynonymsForTargetDatabase (
    @databaseName sysname
)
AS BEGIN
DECLARE @TSQL nvarchar(max) = N''
DECLARE @rn char(2),
    @SchemaName sysname;

    SET @rn = char(13) + char(10)   

    CREATE TABLE #DBSynonym(        
        [Schema] sysname NOT NULL,
        [Table] sysname NOT NULL
    )

    SET @TSQL = N'
        INSERT INTO #DBSynonym ([Schema], [Table])
        SELECT Schemas.name, Tables.name
        FROM [' + @databaseName + '].sys.tables 
        INNER JOIN [' + @databaseName + '].sys.schemas on tables.schema_id = schemas.schema_id      
    '

    EXEC (@TSQL)
    SET @TSQL = N''

    DECLARE MissingSchemasCursor CURSOR
    READ_ONLY
    FOR 
        SELECT newSchemas.[Schema]
        FROM #DBSynonym newSchemas
        LEFT JOIN sys.schemas on newSchemas.[Schema] = schemas.name
        WHERE schemas.schema_id is null
        GROUP BY newSchemas.[Schema]

    OPEN MissingSchemasCursor
    FETCH NEXT FROM MissingSchemasCursor INTO @SchemaName
    WHILE (@@fetch_status <> -1)
    BEGIN
        IF (@@fetch_status <> -2)
        BEGIN
            SET @TSQL = N'CREATE SCHEMA ' + QUOTENAME(@SchemaName) + N';'

            EXEC sp_executesql @TSQL
        END
        FETCH NEXT FROM MissingSchemasCursor INTO @SchemaName
    END
    CLOSE MissingSchemasCursor
    DEALLOCATE MissingSchemasCursor

    /*
    SELECT @TSQL = @TSQL +
        N'
        GO
        CREATE SCHEMA ' + QUOTENAME([Schema]) + N';'
    FROM #DBSynonym newSchemas
    LEFT JOIN sys.schemas on newSchemas.[Schema] = schemas.name
    WHERE schemas.schema_id is null
    GROUP BY newSchemas.[Schema]

    PRINT 'CREATE SCHEMAS : ' + ISNULL(@TSQL,'')
    EXEC sp_executesql @TSQL
    */
    SET @TSQL = N''

    SELECT @TSQL = @TSQL +
        N'
        CREATE SYNONYM ' + QUOTENAME([Schema]) + N'.' + QUOTENAME([Table]) + N'
        FOR ' + QUOTENAME(@databaseName) + N'.' + QUOTENAME([Schema]) + N'.' + QUOTENAME([Table]) + N';'
    FROM #DBSynonym


    EXEC sp_executesql @TSQL
    SET @TSQL = N''

END
GO

Use it as follows :

EXEC CreateSynonymsForTargetDatabase 'targetDbName'

Answer 4

The question is: how we can create an alias of for database?

I know this is an old post but...

This is why I only use the 2 part naming convention for SQL objects. It allows me to have 2 part synonyms that point to differently named databases depending on what environment I'm in. There are some places where it doesn't work so well but, for the most part, those places are very rare.

As for software that you don't have the source code of and if that software uses the 3 part naming convention, you're probably just out of luck unless you know what the 3 part naming convention is for each object and create a 3 part synonym for each object.

Answer 5

I found Charles' answer (and the linked workaround in the comment by maxcastaneda) very useful. I followed this approach and it works for me. I have streamlined it a bit and created the following query that brings up all required synonyms to create.

As a prerequisite for this snippet both the original DB and the synonym/alias db have to be on the same server otherwise in case you use linked server or so you have to modify it a bit. It should be fairly easy to put this into a small sp to update the synonyms automatically.

USE <SYNONYMDB>
SELECT 
'[' + TABLE_NAME + ']', 
'[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']',
'IF EXISTS (SELECT * FROM sys.synonyms WHERE name = ''' + TABLE_NAME + ''') DROP SYNONYM ['+ TABLE_NAME + '];   CREATE SYNONYM [' + TABLE_NAME + '] FOR <ORIGINALDB>.' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']' AS SynonymUpdateScript FROM <ORIGINALDB>.INFORMATION_SCHEMA.TABLES

Don't forget to enter you Db names at the <...> spots.

Just copy the content of the SynonymUpdateScript Column and execute it in the synonym DB - or create a stored procedure for this task.

Be aware there is an issue if you have views in place that refer to tables or other db objects without the 2 part naming convention. Those synonyms won't work. You should fix this in the original objects / views.

Answer 6

1. Go to the Database you wish to create Alias,

2. Create an Alias Folders table with the preferred design,

3. Go to unique IDs's table and check the last code sequence for the table created.

   For example, if the last code is 10, then update it to 11.

4. Open Cabinets table and go right at the bottom and create the name of the Alias cabinet you want.

Answer 7

You can create an alias from 'SQL Server Configuration Manager' under Configuartion Tool in SQL Server Folder.

Detailed source : http://www.mssqltips.com/sqlservertip/1620/how-to-setup-and-use-a-sql-server-alias/

http://technet.microsoft.com/en-us/library/ms190445.aspx