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

背景：

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

语法

-- SQL Server SyntaxCREATE 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 SyntaxCREATE 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]GOCREATE 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%)GOALTER DATABASE [isoft_Avon_20170730] SET COMPATIBILITY_LEVEL = 110GOIF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))beginEXEC [isoft_Avon_20170730].[dbo].[sp_fulltext_database] @action = 'enable'endGOALTER DATABASE [isoft_Avon_20170730] SET ANSI_NULL_DEFAULT OFF GOALTER DATABASE [isoft_Avon_20170730] SET ANSI_NULLS OFF GOALTER DATABASE [isoft_Avon_20170730] SET ANSI_PADDING OFF GOALTER DATABASE [isoft_Avon_20170730] SET ANSI_WARNINGS OFF GOALTER DATABASE [isoft_Avon_20170730] SET ARITHABORT OFF GOALTER DATABASE [isoft_Avon_20170730] SET AUTO_CLOSE OFF GOALTER DATABASE [isoft_Avon_20170730] SET AUTO_CREATE_STATISTICS ON GOALTER DATABASE [isoft_Avon_20170730] SET AUTO_SHRINK OFF GOALTER DATABASE [isoft_Avon_20170730] SET AUTO_UPDATE_STATISTICS ON GOALTER DATABASE [isoft_Avon_20170730] SET CURSOR_CLOSE_ON_COMMIT OFF GOALTER DATABASE [isoft_Avon_20170730] SET CURSOR_DEFAULT  GLOBAL GOALTER DATABASE [isoft_Avon_20170730] SET CONCAT_NULL_YIELDS_NULL OFF GOALTER DATABASE [isoft_Avon_20170730] SET NUMERIC_ROUNDABORT OFF GOALTER DATABASE [isoft_Avon_20170730] SET QUOTED_IDENTIFIER OFF GOALTER DATABASE [isoft_Avon_20170730] SET RECURSIVE_TRIGGERS OFF GOALTER DATABASE [isoft_Avon_20170730] SET  ENABLE_BROKER GOALTER DATABASE [isoft_Avon_20170730] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GOALTER DATABASE [isoft_Avon_20170730] SET DATE_CORRELATION_OPTIMIZATION OFF GOALTER DATABASE [isoft_Avon_20170730] SET TRUSTWORTHY OFF GOALTER DATABASE [isoft_Avon_20170730] SET ALLOW_SNAPSHOT_ISOLATION OFF GOALTER DATABASE [isoft_Avon_20170730] SET PARAMETERIZATION SIMPLE GOALTER DATABASE [isoft_Avon_20170730] SET READ_COMMITTED_SNAPSHOT OFF GOALTER DATABASE [isoft_Avon_20170730] SET HONOR_BROKER_PRIORITY OFF GOALTER DATABASE [isoft_Avon_20170730] SET RECOVERY FULL GOALTER DATABASE [isoft_Avon_20170730] SET  MULTI_USER GOALTER DATABASE [isoft_Avon_20170730] SET PAGE_VERIFY CHECKSUM  GOALTER DATABASE [isoft_Avon_20170730] SET DB_CHAINING OFF GOALTER DATABASE [isoft_Avon_20170730] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) GOALTER DATABASE [isoft_Avon_20170730] SET TARGET_RECOVERY_TIME = 0 SECONDS GOALTER DATABASE [isoft_Avon_20170730] SET  READ_WRITE GO

• 创建场景

USE [master]GOCREATE LOGIN [TestLogin1] WITH PASSWORD = N'123456',DEFAULT_DATABASE = [isoft_Avon_20170730],DEFAULT_LANGUAGE = [us_english]GOCREATE LOGIN [TestLogin2] WITH PASSWORD = N'123456',DEFAULT_DATABASE = [isoft_Avon_20170730],DEFAULT_LANGUAGE = [us_english]GO USE [isoft_Avon_20170730]GOCREATE USER [TestUser1] FOR LOGIN [TestLogin1] WITH DEFAULT_SCHEMA = BMSSAGOCREATE USER [TestUser2] FOR LOGIN [TestLogin2] WITH DEFAULT_SCHEMA = ComacGO GOCREATE SCHEMA BMSSA AUTHORIZATION [TestUser1]GOCREATE 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] GOCREATE 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] GOCREATE TRIGGER tgr_Gomac_Table1 ON Gomac.TABLE1 FOR INSERT,UPDATE,delete  ASBEGINSET NOCOUNT ON ;MERGE BMSSA.tablE1 AS target_tbl USING( SELECT F1,F2,F3  FROM Gomac.TABLE1 WITH(NOLOCK))  AS source_tblON target_tbl.F1=source_tbl.F1/*DELETE */WHEN NOT MATCHED BY SOURCETHEN DELETE/*INSERT*/WHEN NOT MATCHEDTHEN INSERT    VALUES(source_tbl.F1,source_tbl.F2,source_tbl.F3)/*UPDATE*/WHEN MATCHEDTHEN UPDATE SET target_tbl.F2=source_tbl.F2,target_tbl.F3=source_tbl.F3;END GOGRANT SELECT, INSERT, DELETE, UPDATE ON SCHEMA :: Gomac TO [TestUser2]GRANT SELECT, INSERT, DELETE, UPDATE ON SCHEMA :: BMSSA TO [TestUser2]GO

• 测试数据

USE isoft_Avon_20170730GOtruncate 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<=100000GOSELECT '-------------------------------Total' AS msgGOSELECT COUNT(*) as c_bmssa FROM BMSSA.TABLE1 AS t SELECT COUNT(*) as c_gomac  FROM gomac.TABLE1 AS t SELECT '-------------------------------AFTER DELETE 100' AS msgGODELETE 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 msgGOUPDATE 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]GOCREATE SYNONYM xxx.[table1] FOR  [isoft_Avon_20170730].Bmssa.[TABLE1]GOSELECT * FROM [isoft_Avon_20170730].[BMSSA].[TABLE1] SELECT * FROM [isoft_Avon_20170730].xxx.[TABLE1]GO--注意schema xxx下能同时存在名为table1的表和名为table1的同义词，否则会产生同名冲突--一般情况下schema xxx只用作名字转换，该schema下不创建物理表