Sql Server : Create & Drop Synonyms

Quick one today:

In a previous post, we saw the power and beauty of synonyms. In this article, we’ll cover some T-SQL aspects of synonyms.

Create Synonym

Create statement for synonym is quite simple:

--
--  CREATE a synonym
--
CREATE SYNONYM Results FOR Monitor.AccessMonitor
GO

 

Alter Synonym

As we know, from previous post, there is no ALTER statement for Synonyms, so we’ll need to DROP and CREATE for any modifications.

 

Drop Synonym

Drop statement is straightforward — just like any other object

 
-- 
-- DROP a synonym 
-- 
DROP SYNONYM Results
GO 

 

Hope this helps,

_Sqltimes

Read Full Post »

Sql Server: A Short Primer on Synonyms

Interesting one today:

Synonyms are an integral part of Sql Server for many years. They are quite handy in many cases. Today we’ll look at how to create, remove and manage them.

A short primer:

A synonym is a database object that provides a couple of benefits:

• Provides a new user friendly name to an existing object name
  • Rather than a long name like dbo.AnImportantTableThatHasPreviousEmployeesData we could have a synonym called dbo.ExEmployees
• Provides abstraction layer from the client application knowing the actual name or location of the database object
  • A table called [OtherServer].[OtherDB].[Sales].Employees could just be Employees. And the end client does not need to know where the table actually lives.

We could create Synonyms for a lot of object types:

1. Tables
2. Stored Procedures
3. Sclar Functions
4. Table Valued Functions
5. Views
6. CLR objects
7. And more

Caveats:

1. A base object for a synonym cannot be another synonym
2. Synonym cannot be used when performing DDL on the base object
3. Synonyms are not schema-bound. So base objects could be deleted and re-created with the same name and the synonym would have no clue of these changes. Now it just points to the new object.
4. There is no ALTER statement, so for any changes, we need to DROP and CREATE

Synonym belongs to the schema in which it is created. If you want to query a synonym located in a different schema, then you want to use schema prefix before calling the synonym. So a synonym name needs to be unique within the same schema.

For example:

If the default schema for my login is dbo; To call the MyEmployees synonym located in OtherSchema, then the following is the proper syntax (to call it from my login, which defaults to dbo schema):

OtherSchema.MyEmployees

System table, sys.synonyms provides a list of existing synonyms in the database.

For more information, please refer to BoL

Hope this helps,

_Sqltimes

Read Full Post »

Sql Server : Query Stored Procedure Definition using T-SQL

Quick one today:

Occasionally, when playing with metadata, we find ourselves in need to get Stored Procedures definition. For example, when you need to find all the procedures that call a particular table (or a synonym). Yes, there are other ways to gather this data, but doing the same with T-SQL is more fun and given refined controls.

Query objects using object name (but not table name):

Example:

--
-- Query objects using object name (but not table name)
--
SELECT O.object_id, O.name, O.type_desc, M.definition
FROM sys.objects AS O
LEFT OUTER JOIN sys.sql_modules AS M
ON M.object_id = O.object_id
WHERE O.name LIKE '%Object_Name%'
OR O.name LIKE '%Object_Name_but_not_table_name%'
GO

Query objects using any object name, including table name:

 
-- 
-- Query objects using any object name in the definition (including table name)
-- 
SELECT O.object_id, O.name, O.type_desc, M.definition
FROM sys.objects AS O
LEFT OUTER JOIN sys.sql_modules AS M
ON M.object_id = O.object_id
WHERE M.definition LIKE '%ViewName%'
OR M.definition LIKE '%TableName[.]%'
GO

Cool thing is, this system table sys.sql_modules has definition for more objects :

• Stored Procedures
• DML Triggers
• Scalar Functions
• Table Valued Functions
• Replication-filter-procedure
• Views
• Rules

Hope this helps,
_Sqltimes

Read Full Post »