I have a test database and a development database. Right now I need two versions of all of our scripts because I have a linked server and the name changes between each one in part due to the databases having different names.
How do people get around this? We talked about build tasks that do a find replace, but I was hoping for a better more elegant way.
You could create synonyms for the objects that you need in place of the fully qualified statements. For example:
SELECT * FROM [linked_server_name].[test_database_name].[dbo].[table_1]
...which consists of the following segments...
[linked server].[database].[schema].[object]
Since this is pretty verbose, you could create a synonym for the object from the example above by issuing the following:
CREATE SYNONYM [dbo].[table_1] FOR [linked_server_name].[test_database_name].[dbo].[table_1]
...and your query becomes:
SELECT * FROM [dbo].[table_1]
You can generate a synonym for all tables in your database with the following script and replacing it for your object names:
SELECT 'CREATE SYNONYM ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' FOR [SQL-SOURCE1].' + QUOTENAME(TABLE_CATALOG) + '.' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
FROM [linked_server_name].[test_database_name].[INFORMATION_SCHEMA].Tables
WHERE TABLE_TYPE = 'BASE TABLE'