Codeedoc - Programming Blog

Simulating Oracle Synonyms in MS SQL Server

Oracle Synonyms can be easily simulated in SQL Server by using Views.

The following example creates a view that returns the CarID and CarPurchaseDate from Cars table.

CREATE VIEW vCars
AS
SELECT CarID, CarPurchaseDate
FROM Cars

SELECT statement:

SELECT * FROM vCars

Let’s create a view which is Sql Server version of Oracle’s TABS synonym or USER_TABLES data dictionary view:

CREATE VIEW TABS
AS
SELECT name AS TableName
FROM sysobjects
WHERE type = ‘U’;

Executing the following SELECT statement will return all user table names:

SELECT TableName
FROM TABS