Programmatically creating a table alias

Question

Post reply

Programmatically creating a table alias

Ray K

SSC-Dedicated

Points: 31163
More actions
June 14, 2012 at 9:27 am

#257603

I was surprised to not find this in BOL, and am having a hard time finding this online, so I'm wondering if this is possible . . .
Simple (I hope!) question: what is the syntax to programmatically create a table alias in T-SQL?
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

There is no such object In SQL Server as Table Alias, so you can not create it by any means.

Could you please provide a bit more details about what are you trying to achieve?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 2

table alias meaning synonym? or table alias in the middle of a SQL statement?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ray K SSC-Dedicated Points: 31163 More actions · Answer 3

Lowell (6/14/2012)
table alias meaning synonym? or table alias in the middle of a SQL statement?

Synonym.

For example: let's say I have an existing table called "myTable."

I instead want to refer to it as "AltTable."

So, my SELECT statement would be: select * from AltTable

where AltTable is pointing to (aliasing) myTable.

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Ray K SSC-Dedicated Points: 31163 More actions · Answer 4

I do need to create the table alias as a database object. I might not be using the right terminology.

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Lowell SSC Guru Points: 323518 More actions · Answer 5

easy peasy:

CREATE SYNONYM MailView FOR msdb.dbo.sysmail_allitems

select * from MailView

* note a synonym must point to an object.

so you cannot try to make it part of a name, like getting it to replace LinkedServer.Databasename.Schema.

it has to point to an object, like table, view, etc that you'd find in sys.objects.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ray K SSC-Dedicated Points: 31163 More actions · Answer 6

Ahhh, no wonder why I couldn't find it in BOL!

Thanks, as always!

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Dwain Camps SSC Guru Points: 86948 More actions · Answer 7

Lowell (6/14/2012)
easy peasy:
CREATE SYNONYM MailView FOR msdb.dbo.sysmail_allitems
select * from MailView
* note a synonym must point to an object.
so you cannot try to make it part of a name, like getting it to replace LinkedServer.Databasename.Schema.
it has to point to an object, like table, view, etc that you'd find in sys.objects.

Lowell - Another nice learning experience from you. That's now 2 in as many days!

I see this was introduced in SQL 2005. Any idea if it can be used in SQL 2005 running at compatibility level 80?

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Lowell SSC Guru Points: 323518 More actions · Answer 8

dwain.c (6/14/2012)
Lowell - Another nice learning experience from you. That's now 2 in as many days!
I see this was introduced in SQL 2005. Any idea if it can be used in SQL 2005 running at compatibility level 80?

Dwain i just checked, and it one of those commands that are not filtered based on compatibility level:

even if the database is set for 80, you can create or drop the synonym.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Dwain Camps SSC Guru Points: 86948 More actions · Answer 9

Lowell (6/15/2012)
dwain.c (6/14/2012)
Lowell - Another nice learning experience from you. That's now 2 in as many days!
I see this was introduced in SQL 2005. Any idea if it can be used in SQL 2005 running at compatibility level 80?
Dwain i just checked, and it one of those commands that are not filtered based on compatibility level:
even if the database is set for 80, you can create or drop the synonym.

Well that's cool!

Both that it works and you were able to get back with an answer so quickly. I have a use for it but I was too busy today to check it out for myself.

Thanks.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 10

If you're truly stuck in a pre-2005 environment, you can also create a "pass through" view which will act as if it were a table of a different name (ie. alias).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)