Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Schema Alias in SQL 2005

Posted on 2009-04-14
12
Medium Priority
?
1,054 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
11 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

571 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