SQL Schema Alias in SQL 2005


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
vb7guyAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
RiteshShahConnect With a Mentor Commented:
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
 
RiteshShahCommented:
0
 
James MurrellProduct SpecialistCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
RiteshShahCommented:
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
 
KerjemanovCommented:
You can set dafault schema for any user using this:
USE AdventureWorks;ALTER USER Mary51 WITH DEFAULT_SCHEMA = Purchasing;GO
0
 
vb7guyAuthor Commented:
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
 
vb7guyAuthor Commented:
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
 
RiteshShahCommented:
I guess you can't create synonyms for schema only.

BTW, you don't need to drop it if you don't want
0
 
KerjemanovCommented:
I think, that RiteshShah's is the right answer for the problem. cs97jjm3's answer supplements RiteshShah's answer.
0
 
vb7guyAuthor Commented:
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
 
vb7guyAuthor Commented:
I am accepting RiteshShah's answer since his answer was more accurate.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.