Есть ли простой способ создания синонимов для всех таблиц в другой базе данных?
спасибо
РЕДАКТИРОВАТЬ: I have a number of stored procedures that hardcoded some table schemas into the select queries. When I copy the schemas to a new server, the SPs fail because the schema doesn't exist. There is little control I have over the destination server and I don't want to having to change all the SP, so I thought synonym may be a good solution.
Создайте хранимую процедуру примерно так:
CREATE PROCEDURE SynonymUpdate
@Database nvarchar(256), -- such as 'linkedserver.database' or just 'database'
@Schema sysname -- such as 'dbo'
AS
CREATE TABLE #Tables (
TableID int identity(1,1) NOT NULL PRIMARY KEY CLUSTERED,
Table_Name sysname
)
DECLARE
@SQL nvarchar(4000),
@ID int
SET @SQL = N'SELECT Table_Name FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE Table_Schema = @TableSchema'
INSERT #Tables EXEC sp_executesql @SQL, N'@TableSchema sysname', @Schema
SELECT @ID = MAX(TableID) FROM #Tables
WHILE @ID > 0 BEGIN
SELECT @SQL = 'CREATE SYNONYM ' + Table_Name + ' FOR ' + @Database + '.' + @Schema + '.' + Table_Name FROM #Tables WHERE TableID = @ID
PRINT @SQL
--EXEC sp_executesql @SQL
SET @ID = @ID - 1
END
Затем запустите ее следующим образом:
EXEC SynonymUpdate 'Database' , 'dbo'
Обратите внимание, что вы должны запускать ее от имени пользователя с правом создавать синонимы. Если вы хотите, чтобы пользователь без этих привилегий запускал его, в SQL 2000 не повезло, в SQL 2005 вы можете поместить туда предложение EXECUTE AS.
Если это один экземпляр , и обе базы данных находятся на нем, используйте нотацию с тремя именами:
SELECT *
FROM database1.dbo.table_X
JOIN database2.dbo.table_Y ...
Если другая база данных существует на отдельном экземпляре SQL Server (вы можете иметь более одного SQL Server на одном box), или база данных существует в экземпляре SQL Server в другом ящике/VM — создайте экземпляр Linked Server. Затем вы используете нотацию с четырьмя именами:
SELECT *
FROM database1.dbo.table_X
JOIN linked_server_name.database2.dbo.table_Y
Вы можете выполнить подобный запрос в исходной базе данных, а затем запустить выходные результаты в новой базе данных.
select 'create synonym syn_' + t.name + ' for [' + DB_NAME() + '].[' + s.name + '].[' + t.name + ']'
from sys.tables t
inner join sys.schemas s
on t.schema_id = s.schema_id
where t.type = 'U'
В качестве примера, запуск этого в базе данных Master даст:
create synonym syn_spt_fallback_db for [master].[dbo].[spt_fallback_db]
create synonym syn_spt_fallback_dev for [master].[dbo].[spt_fallback_dev]
create synonym syn_spt_fallback_usg for [master].[dbo].[spt_fallback_usg]
create synonym syn_spt_monitor for [master].[dbo].[spt_monitor]
create synonym syn_spt_values for [master].[dbo].[spt_values]
create synonym syn_MSreplication_options for [master].[dbo].[MSreplication_options]