Execute the following Microsoft SQL Server Transact-SQL (T-SQL) script in Management Studio (SSMS) Query Editor, SQLCMD or other client software to demonstrate the use of synonyms in SQL Server 2005:

use tempdb
go

Create Schema Carneval
go
Create Schema TradeShow
go
Create table tempdb.Carneval.City (CityID int identity(1,1), City varchar(200))
go
insert into tempdb.Carneval.City select 'Houston'
insert into tempdb.Carneval.City select 'New York'
insert into tempdb.Carneval.City select 'Chicago'
insert into tempdb.Carneval.City select 'Buffalo'
insert into tempdb.Carneval.City select 'Denver'
insert into tempdb.Carneval.City select 'Miami'
insert into tempdb.Carneval.City select 'New Orleans'
insert into tempdb.Carneval.City select 'Seattle'
insert into tempdb.Carneval.City select 'Philadelphia'
insert into tempdb.Carneval.City select 'Washington'

go
Create procedure Carneval.DisplayCity @CityID int
as
Select City from tempdb.Carneval.City where CityID=@CityID
go
Create table tempdb.TradeShow.City (CityID int identity(1,1), City varchar(200))
go
insert into tempdb.TradeShow.City select 'Las Vegas'
insert into tempdb.TradeShow.City select 'Miami'
insert into tempdb.TradeShow.City select 'Los Angeles'
insert into tempdb.TradeShow.City select 'San Francisco'
insert into tempdb.TradeShow.City select 'Chicago'

go
Create procedure TradeShow.DisplayCity @id int
as
Select City from tempdb.TradeShow.City where CityID=@id
go

select * from tempdb.Carneval.City
go
select * from tempdb.TradeShow.City

create SYNONYM CarnevalCity for tempdb.Carneval.City
go
create SYNONYM TradeShowCity for tempdb.TradeShow.City
go

Select * from CarnevalCity
Select * from TradeShowCity
Go

create SYNONYM ShowCarnevalCity for tempdb.Carneval.DisplayCity
go
create SYNONYM ShowTradeShowCity for tempdb.TradeShow.DisplayCity
go

exec ShowCarnevalCity 2
go

exec ShowTradeShowCity 5
go