SQL 2005 Synonyms

--Title:sql Server2005 synonym use

-author:wufeng4552

-Date:2009-10-30

1.Synonym the concept
Synonym (synonym) is a new feature of SQL Server 2005. You can easily understand the aliases of synonym for other images.

Language method

CREATE synonym [Schema_name_1.] Synonym_name for < object >

< OBJECT >:: =

{

[Server_Name. [database_name]. [Schema_name_2].| database_name. [Schema_name_2].| Schema_name_2. ]object_name

}

The base object does not need to exist when creating the same word. At the executive stage, SQL Server checks to see if the base object exists.

The parameters are as follows:

Schema_name_1: Specifies a description of the structure that establishes the definition of the same word. If you do not specify Schema,sql Server 2005, you will use the current user's preset description.

Synonym_name: This is the name of the new synonyms.

SERVER_NAME: This is the name of the server where the base object resides.

database_name: This is the name of the repository where the base object resides. If database_name is not specified, the name of the current repository is used.

Schema_name_2: This is the structure description name of the base object. If schema_name is not specified, the current user's preset description is used.

Object_name: This is the name of the base object in the same definition.

Note:

To create a synonym using a given schema, the user must meet the following criteria:

(1) Having this schema or having ALTER schema permission to

(2) have CREATE synonym permission.

(3) is a member of the db_owner.

(4) is a member of the db_ddladmin fixed database role

You can create synonyms for the following object types:
Assembly (CLR) stored procedures; Assembly (CLR) table-valued functions
Assembly (CLR) scalar functions; Program Clustering (CLR) aggregation functions
replication filtering process; Extended stored Procedures
SQL scalar functions; SQL table-valued functions SQL inline table-valued functions;
SQL stored procedure view; Table (user-defined)
Practical Application of 2.Synonym

When your program is published, you suddenly find that you need to change a table name, or field name. And your program is impossible to modify. At this time, how to do? Then create a synonym. Of course, in the sql2000 era, you can use view to do this thing, or SPROCs or UDF. But synonym has other functions that are not available, that is, cross-database, cross-server.

----------------------------------------------------------------------------

--synonym different databases on the same server

Use Db_study

Go

If object_id (' mycustomers ') is not null drop synonym mycustomers

Go

-Creating names for tables

Create synonym mycustomers for Northwind.dbo.Customers

--Enquiry

Go

SELECT * FROM Mycustomers

--Switch to the repository

Use Northwind

Go

--building functions

If object_id (' Synonym_uf ') is not null drop function Synonym_uf

Go

Create function Synonym_uf ()

Returns table

As

Return (SELECT * from Customers)

Go

----Create a name for the function

Use Db_study

Go

If object_id (' Synonym_uf ') is not null drop synonym Synonym_uf

Go

Create synonym Synonym_uf for Northwind.dbo.synonym_UF

Go

--Adjust function

SELECT * FROM Synonym_uf ()

--Establish storage process

If object_id (' Synonym_pro ') is not null drop proc Synonym_pro

Go

Create proc Synonym_pro

As

SELECT * FROM Customers

Go

----Create names for storage

Use Db_study

Go

If object_id (' Synonym_pro ') is not null drop synonym Synonym_pro

Go

Create synonym Synonym_pro for Northwind.dbo.synonym_PRO

Go

--Perform storage process

EXEC Synonym_pro

--Support the same motion

If object_id (' mycustomers ') is not null drop synonym mycustomers

Go

-Creating names for tables

EXEC (' Create synonym mycustomers for Northwind.dbo.Customers ')

--Enquiry

Go

SELECT * FROM Mycustomers

----------------------------------------------------------------------------

---synonym different databases on different servers

--Set up a link server first

--Create a linked server

exec sp_dropserver ' itsv ', ' droplogins '

exec sp_addlinkedserver ' itsv ', ', ' SQLOLEDB ', ' 192.168.12.***/wip '

exec sp_addlinkedsrvlogin ' itsv ', ' false ', NULL, ' sa ', ' sun*****% '

Go

Create synonym mycustomers for itsv. Northiwind.dbo.Customers

Go

Select * from Mycustomers