Solved

SQL Schema Alias in SQL 2005

Posted on 2009-04-14
12
1,036 Views
Last Modified: 2012-05-06

Hi, we have bunch of Schemas in our production databases (SQL 2005).  The problem is that most of the schemas have very long name. So every time we run a SQL statement we have to type the name of the schema where the table resides. "PublicfacingHouse.Clients" I don't really want to rename the schema.  But is there a way to create a link or Alias with much shorter name.
For example
Schema Name: PublicFacingHouse.Clients
Alias name would be like PH.Clients
0
Comment
Question by:vb7guy
  • 4
  • 4
  • 2
  • +1
12 Comments
 
LVL 31

Expert Comment

by:RiteshShah
Comment Utility
0
 
LVL 31

Expert Comment

by:James Murrell
Comment Utility
great article here http://articles.techrepublic.com.com/5100-10878_11-6174486.html

A synonym is a new object to SQL Server 2005. It is a way to give an alias to an already existing object.
0
 
LVL 31

Expert Comment

by:RiteshShah
Comment Utility
two more in-depth article.

http://www.developer.com/db/article.php/3613301

http://msdn.microsoft.com/en-us/library/ms177544(SQL.90).aspx

actually in my article, it was just a small example with definition but above two articles are really in-depth.
0
 
LVL 1

Expert Comment

by:Kerjemanov
Comment Utility
You can set dafault schema for any user using this:
USE AdventureWorks;ALTER USER Mary51 WITH DEFAULT_SCHEMA = Purchasing;GO
0
 

Author Comment

by:vb7guy
Comment Utility
Kerjemanov: Default schema may work in my situation.  We have multiple schemas with long names.
Base on our business rules we have divided the tables and put them into different schema.
 I was hoping to have shortername alias that points to a particular schema.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:vb7guy
Comment Utility
I'm reading through these articles. They all talk about creating a SYNONYM to an object. Object being a table.  But in my case we have so many tables, I would rather create a SYNONYM to just the schema name.  

And do we have to drop SYNONYM after short period of time.
0
 
LVL 31

Expert Comment

by:RiteshShah
Comment Utility
I guess you can't create synonyms for schema only.

BTW, you don't need to drop it if you don't want
0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 500 total points
Comment Utility
author wanted alias for schema only which is not at all possible, I have told that in my post 24140541. most possible thing was to create alias for some object in SQL Server which I have described in my post 24140162 and 24140216
0
 
LVL 1

Expert Comment

by:Kerjemanov
Comment Utility
I think, that RiteshShah's is the right answer for the problem. cs97jjm3's answer supplements RiteshShah's answer.
0
 

Author Comment

by:vb7guy
Comment Utility
Sorry for not responding to the answers.  But RiteshShah's Answer worked for me. I figured out a way to create SYNONYM for all tables.

Here is an example of how I created a script to create for all tables.  We have several Several Scema. One of them called "DB_Legacy" which shares the some of the same table names. That's why I wanted to create Synonyms with a "LG_" prefix.  Because you can not have dublicate Synonyms since they are all created under "dbo." schema.

--Create Synonyms for all tables in the database where schema name is not "DB_Legacy"
SELECT   TABLE_SCHEMA,TABLE_NAME, ('CREATE SYNONYM ' +  TABLE_NAME  + ' FOR ' + TABLE_SCHEMA + '.' + TABLE_NAME  ) AS NEWCODE FROM INFORMATION_SCHEMA.TABLES where table_Type <> 'View' AND TABLE_SCHEMA <> 'dbo' and table_name not like '%-%' AND Table_schema <> 'DB_Legacy' ORDER BY TABLE_SCHEMA

--Create Synonyms for tables in the "DB_Legacy" Schema "LG_" is the prefix because some table name are the same as other Schema
SELECT   TABLE_SCHEMA,TABLE_NAME, ('CREATE SYNONYM ' +  'LG_' + TABLE_NAME  + ' FOR ' + TABLE_SCHEMA + '.' + TABLE_NAME  ) AS NEWCODE FROM INFORMATION_SCHEMA.TABLES where table_Type <> 'View' AND TABLE_SCHEMA <> 'dbo' and table_name not like '%-%' AND Table_schema = 'DB_Legacy' ORDER BY TABLE_SCHEMA
0
 

Author Closing Comment

by:vb7guy
Comment Utility
I am accepting RiteshShah's answer since his answer was more accurate.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now