Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL Schema Alias in SQL 2005

Posted on 2009-04-14
12
1,043 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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
 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

791 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