sql server create synonym 用同义词解决程序升级过程中架构名称改变的问题-CSDN博客

本文链接：https://blog.csdn.net/wangzhpwang/article/details/38311335

背景：

公司有个erp 升级数据库从oracle 升到sql server 2012 Erp中的query等改写死左schema且修改起来比较困难，同义词在此起到不改变架构名称去访问其它架构基础对象的作用。

语法

 
     -- SQL Server Syntax

CREATE SYNONYM [ schema_name_1. ] synonym_name FOR <object>

<object> :: =
{
    [ server_name.[ database_name ] . [ schema_name_2 ]. object_name 
  |  database_name . [ schema_name_2 ].| schema_name_2. ] object_name
}

 
     -- Windows Azure SQL Database Syntax

CREATE SYNONYM [ schema_name_1. ] synonym_name FOR < object >

< object > :: =
{
    [database_name. [ schema_name_2 ].| schema_name_2. ] object_name
}

详细请查看： http://msdn.microsoft.com/zh-cn/library/ms177544.aspx

实例

创建数据库

USE [master]
GO
CREATE DATABASE [isoft_Avon_20170730]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'isoft_Avon_20170730', FILENAME = N'd:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\isoft_Avon_20170730.mdf' , SIZE = 4160KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'isoft_Avon_20170730_log', FILENAME = N'd:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\isoft_Avon_20170730_log.ldf' , SIZE = 1040KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [isoft_Avon_20170730] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
	EXEC [isoft_Avon_20170730].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [isoft_Avon_20170730] SET ANSI_NULL_DEFAULT OFF 
GO

ALTER DATABASE [isoft_Avon_20170730] SET ANSI_NULLS OFF 
GO

ALTER DATABASE [isoft_Avon_20170730] SET ANSI_PADDING OFF 
GO

ALTER DATABASE [isoft_Avon_20170730] SET ANSI_WARNINGS OFF 
GO

ALTER DATABASE [isoft_Avon_20170730] SET ARITHABORT OFF 
GO

ALTER DATABASE [isoft_Avon_20170730] SET AUTO_CLOSE OFF 
GO

ALTER DATABASE [isoft_Avon_20170730] SET AUTO_CREATE_STATISTICS ON 
GO

ALTER DATABASE [isoft_Avon_20170730] SET AUTO_SHRINK OFF 
GO

ALTER DATABASE [isoft_Avon_20170730] SET AUTO_UPDATE_STATISTICS ON 
GO

ALTER DATABASE [isoft_Avon_20170730] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO

ALTER DATABASE [isoft_Avon_20170730] SET CURSOR_DEFAULT  GLOBAL 
GO

ALTER DATABASE [isoft_Avon_20170730] SET CONCAT_NULL_YIELDS_NULL OFF 
GO

ALTER DATABASE [isoft_Avon_20170730] SET NUMERIC_ROUNDABORT OFF 
GO

ALTER DATABASE [isoft_Avon_20170730] SET QUOTED_IDENTIFIER OFF 
GO

ALTER DATABASE [isoft_Avon_20170730] SET RECURSIVE_TRIGGERS OFF 
GO

ALTER DATABASE [isoft_Avon_20170730] SET  ENABLE_BROKER 
GO

ALTER DATABASE [isoft_Avon_20170730] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO

ALTER DATABASE [isoft_Avon_20170730] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO

ALTER DATABASE [isoft_Avon_20170730] SET TRUSTWORTHY OFF 
GO

ALTER DATABASE [isoft_Avon_20170730] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO

ALTER DATABASE [isoft_Avon_20170730] SET PARAMETERIZATION SIMPLE 
GO

ALTER DATABASE [isoft_Avon_20170730] SET READ_COMMITTED_SNAPSHOT OFF 
GO

ALTER DATABASE [isoft_Avon_20170730] SET HONOR_BROKER_PRIORITY OFF 
GO

ALTER DATABASE [isoft_Avon_20170730] SET RECOVERY FULL 
GO

ALTER DATABASE [isoft_Avon_20170730] SET  MULTI_USER 
GO

ALTER DATABASE [isoft_Avon_20170730] SET PAGE_VERIFY CHECKSUM  
GO

ALTER DATABASE [isoft_Avon_20170730] SET DB_CHAINING OFF 
GO

ALTER DATABASE [isoft_Avon_20170730] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO

ALTER DATABASE [isoft_Avon_20170730] SET TARGET_RECOVERY_TIME = 0 SECONDS 
GO

ALTER DATABASE [isoft_Avon_20170730] SET  READ_WRITE 
GO

创建场景

USE [master]
GO
CREATE LOGIN [TestLogin1] 
WITH PASSWORD = N'123456',DEFAULT_DATABASE = [isoft_Avon_20170730],DEFAULT_LANGUAGE = [us_english]
GO
CREATE LOGIN [TestLogin2] 
WITH PASSWORD = N'123456',DEFAULT_DATABASE = [isoft_Avon_20170730],DEFAULT_LANGUAGE = [us_english]
GO
 
USE [isoft_Avon_20170730]
GO
CREATE USER [TestUser1] FOR LOGIN [TestLogin1] 
WITH DEFAULT_SCHEMA = BMSSA
GO
CREATE USER [TestUser2] FOR LOGIN [TestLogin2] 
WITH DEFAULT_SCHEMA = Comac
GO
 
GO
CREATE SCHEMA BMSSA AUTHORIZATION [TestUser1]
GO
CREATE SCHEMA Gomac AUTHORIZATION [TestUser1]
GO
 
CREATE TABLE BMSSA.TABLE1(
F1  UNIQUEIDENTIFIER NOT NULL,
F2 INT NOT NULL,
F3 INT NOT NULL	
 CONSTRAINT [PK_F1] PRIMARY KEY CLUSTERED 
(
	[F1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] 
GO

CREATE TABLE GOMAC.TABLE1(
F1  UNIQUEIDENTIFIER NOT NULL ,
F2 INT NOT NULL,
F3 INT NOT NULL	
 CONSTRAINT [PK_F1] PRIMARY KEY CLUSTERED 
(
	[F1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] 
GO

CREATE TRIGGER tgr_Gomac_Table1 ON Gomac.TABLE1 FOR INSERT,UPDATE,delete  AS
BEGIN
	SET NOCOUNT ON ;
	
	MERGE BMSSA.tablE1 AS target_tbl 
	USING
		( SELECT F1,F2,F3  FROM Gomac.TABLE1 WITH(NOLOCK))  AS source_tbl
	ON target_tbl.F1=source_tbl.F1
	/*DELETE */
	WHEN NOT MATCHED BY SOURCE
	THEN DELETE	
	/*INSERT*/
	WHEN NOT MATCHED
	THEN INSERT    VALUES(source_tbl.F1,source_tbl.F2,source_tbl.F3)
	/*UPDATE*/
	WHEN MATCHED
	THEN UPDATE SET 	target_tbl.F2=source_tbl.F2,
						target_tbl.F3=source_tbl.F3;
END 
GO
GRANT SELECT, INSERT, DELETE, UPDATE ON SCHEMA :: Gomac TO [TestUser2]
GRANT SELECT, INSERT, DELETE, UPDATE ON SCHEMA :: BMSSA TO [TestUser2]
GO

测试数据

USE isoft_Avon_20170730
GO
truncate table Gomac.table1 
go
/*Test DATA*/
;WITH
    L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
    L1 AS (SELECT 1 AS c FROM L0 AS a,L0 AS b),
    L2 AS (SELECT 1 AS c FROM L1 AS a,L1 AS b),
    L3 AS (SELECT 1 AS c FROM L2 AS a,L2 AS b),
    L4 AS (SELECT 1 AS c FROM L3 AS a,L3 AS b),
    L5 AS (SELECT 1 AS c FROM L4 AS a,L4 AS b), 
    nums AS (SELECT ROW_NUMBER() OVER (ORDER BY c)AS n FROM L5)
INSERT INTO gomac.TABLE1
    SELECT  newid() as F1,N+1 AS F2,N+2 AS F3  FROM nums WHERE n<=100000
GO
SELECT '-------------------------------Total' AS msg
GO
SELECT COUNT(*) as c_bmssa FROM BMSSA.TABLE1 AS t 
SELECT COUNT(*) as c_gomac  FROM gomac.TABLE1 AS t 
SELECT '-------------------------------AFTER DELETE 100' AS msg
GO
DELETE TOP (100) FROM Gomac.table1  
SELECT COUNT(*) as  c_bmssa   FROM BMSSA.TABLE1 AS t 
SELECT COUNT(*) as  c_gomac   FROM gomac.TABLE1 AS t 
SELECT '-------------------------------AFTER UPDATE 10' AS msg
GO
UPDATE TOP (10) Gomac.table1  SET F2=0,F3=0 
SELECT TOP (10) *FROM BMSSA.TABLE1 AS t 
SELECT TOP (10) *FROM gomac.TABLE1 AS t

解决

现在有query select * from [isoft_Avon_20170730].xxx.[TABLE1]

要访问 [isoft_Avon_20170730].BMSSA.[TABLE1]里的数据，那么：

CREATE SCHEMA xxx AUTHORIZATION [TestUser1]
GO
CREATE SYNONYM xxx.[table1] 
	FOR  [isoft_Avon_20170730].Bmssa.[TABLE1]
	GO
SELECT * FROM [isoft_Avon_20170730].[BMSSA].[TABLE1] 
SELECT * FROM [isoft_Avon_20170730].xxx.[TABLE1]
GO
--注意schema xxx下能同时存在名为table1的表和名为table1的同义词，否则会产生同名冲突
--一般情况下schema xxx只用作名字转换，该schema下不创建物理表