create synonym to schema

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » create synonym to schema

create synonym to schema

Rate Topic

Display Mode

Topic Options

Author

Message

erics44

Posted Monday, January 28, 2013 8:18 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, May 20, 2013 7:27 AM
Points: 266, Visits: 512

Hi
I have a dev database and a live database

both have linked servers to other databases in the dev environments and live environments

I would like to know if it is posible to create a synonym to a schema rather than an object

so In the dev database my synonym would be created something like

create sysnonym Server.TheSchema for [DevServer].[Databade].[TheSchema]

and in live

create sysnonym Server.TheSchema for [LiveServer].[Databade].[TheSchema]

This way I would only need to alter the information in my synonym rather than each time i reference a table.

I know the about doesnt work so i was hoping that there was a way.

Thanks

Post #1412457

Lowell

Posted Monday, January 28, 2013 8:23 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:33 PM
Points: 11,648, Visits: 27,768

nope a synonym must point to an object ( an object that would exist in a databases sys.objects view), and not part of the name of an object.

so by definitions, it is typically used for tables , views etc. very handy for aliasing four part objects, like CREATE SYNONYM myTable FOR OtherServer.Databasename.dbo.Tablename

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1412459

anthony.green

Posted Monday, January 28, 2013 8:25 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Synonyms can be created for the following types of objects:

Assembly (CLR) Stored Procedure
Assembly (CLR) Table-valued Function
Assembly (CLR) Scalar Function
Assembly Aggregate (CLR) Aggregate Functions
Replication-filter-procedure
Extended Stored Procedure
SQL Scalar Function
SQL Table-valued Function
SQL Inline-table-valued Function
SQL Stored Procedure
View
Table (User defined) - Includes local and global temporary tables

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1412461

erics44

Posted Monday, January 28, 2013 9:15 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, May 20, 2013 7:27 AM
Points: 266, Visits: 512

thanks a lot for the replies guys

Post #1412495

ScottPletcher

Posted Monday, January 28, 2013 10:12 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 3:56 PM
Points: 1,324, Visits: 1,778

Nope, you have to synonym every object:

[LiveServer].[Databade].[TheSchema].[ObjectName1] --> [DevServer].[Databade].[TheSchema].[ObjectName1]

[LiveServer].[Databade].[TheSchema].[ObjectName2] --> [DevServer].[Databade].[TheSchema].[ObjectName2]

etc.

SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson

Post #1412533

« Prev Topic | Next Topic »

Permissions