SQL SERVER 2005中同义词实例

王朝mssql·作者佚名  2008-05-30
窄屏简体版  字體: |||超大  

在SQL SERVER 2005中,终于出现了同义词了,大大方便了使用。下面举个小例子说明

同义词是用来实现下列用途的数据库对象:

为本地或远程服务器上的另一个数据库对象(称为“基对象”)提供备选名称。

提供一个提取层,该层防止客户端应用程序的基对象的名称或位置被更改。

例如,名为 Server1 的服务器上有 Adventure Works 的 Employee 表。若要从另一台服务器 Server2 引用此表,客户端应用程序必须使用由四个部分构成的名称 Server1.AdventureWorks.Person.Employee。另外,如果更改了表的位置(例如,更改到另一台服务器上),那么需要修改客户端应用程序以反映所做的更改。 之前在http://www.cnblogs.com/jackyrong/archive/2006/06/15/426304.html中已经归纳了一些特性,现在在举出例子,例子来自老外的。

首先建立两个数据库

Create Database RiverResearch

go

Use RiverResearch

go

然后分别为两个数据库建立模式以及表,存储过程如下

Use RainbowResearch

go

Create Schema Rain

go

Create Schema Snow

go

Create table Rainbowresearch.Rain.Cities (id int, City varchar(200))

go

insert into Rainbowresearch.Rain.Cities select 1, 'Hongkong'

insert into Rainbowresearch.Rain.Cities select 2, 'Tokyo'

insert into Rainbowresearch.Rain.Cities select 3, 'Beijing'

insert into Rainbowresearch.Rain.Cities select 4, 'Taipei'

insert into Rainbowresearch.Rain.Cities select 5, 'Seoul'

insert into Rainbowresearch.Rain.Cities select 6, 'Mumbai'

go

Create procedure Rain.DisplayCities @id int

as

Select City from Rainbowresearch.Rain.Cities where id=@id

go

Create table Rainbowresearch.Snow.Cities (id int, City varchar(200))

go

insert into Rainbowresearch.Snow.Cities select 1, 'Tokyo'

insert into Rainbowresearch.Snow.Cities select 2, 'Seoul'

insert into Rainbowresearch.Snow.Cities select 3, 'Moscow'

insert into Rainbowresearch.Snow.Cities select 4, 'NewYork'

go

Create procedure Snow.DisplayCities @id int

as

Select City from Rainbowresearch.Snow.Cities where id=@id

go

Use RiverResearch

go

Create Schema River

go

go

Create table RiverResearch.River.Cities (id int, City varchar(200))

go

insert into RiverResearch.River.Cities select 1, 'Hongkong'

insert into RiverResearch.River.Cities select 2, 'Tokyo'

insert into RiverResearch.River.Cities select 3, 'Beijing'

insert into RiverResearch.River.Cities select 4, 'Taipei'

insert into RiverResearch.River.Cities select 5, 'Seoul'

go

Create procedure River.DisplayCities @id int

as

Select City from RiverResearch.River.Cities where id=@id

Go

这里,在RainbowResearch数据库里建立了两个模式,rain,snow,再分别在这两个模式下建立了city表,而在

riversearch数据库里也建立了个模式river,也建立了表cities,就这么简单,不详细表述。

之后,我们查询刚才建好的内容

select * from RiverResearch.River.Cities

go

select * from Rainbowresearch.Rain.Cities

go

select * from Rainbowresearch.Snow.Cities

go

最后,我们建立同义词

use RainbowResearch

go

create SYNONYM RiverCities for RiverResearch.River.Cities

go

create SYNONYM RainCities for Rainbowresearch.Rain.Cities

go

create SYNONYM SnowCities for Rainbowresearch.Snow.Cities

go

use RiverResearch

go

create SYNONYM RiverCities for RiverResearch.River.Cities

go

create SYNONYM RainCities for Rainbowresearch.Rain.Cities

go

create SYNONYM SnowCities for Rainbowresearch.Snow.Cities

go

看到了么,简单许多了

再来查询一次,这次简单多了

Use RiverResearch

go

Select * from RiverCities

Select * from RainCities

Select * from SnowCities

Go

Use RainbowResearch

go

Select * from RiverCities

go

Select * from RainCities

go

Select * from SnowCities

go

http://www.cnblogs.com/jackyrong/archive/2006/11/15/561287.html

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航