Solved

SQL Schema Alias in SQL 2005

Posted on 2009-04-14
12
1,038 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
ID: 24140162
0
 
LVL 31

Expert Comment

by:James Murrell
ID: 24140191
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
ID: 24140216
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
ID: 24140224
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
ID: 24140249
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:vb7guy
ID: 24140424
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
ID: 24140541
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
ID: 24343326
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
ID: 24343364
I think, that RiteshShah's is the right answer for the problem. cs97jjm3's answer supplements RiteshShah's answer.
0
 

Author Comment

by:vb7guy
ID: 24354210
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
ID: 31570001
I am accepting RiteshShah's answer since his answer was more accurate.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

863 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

27 Experts available now in Live!

Get 1:1 Help Now