T-SQL Table name alias

Question

In my T-SQL script, I refer to same long table name several times. I use this query on different tables.

Is there a way to refer a table name by variable? If so, I can simple declare one variable at the top which script will use and just by setting value, I can run it on various tables without making changes in the script.

Paul Keister · Accepted Answer · 2011-09-23 00:41:23Z

9

A couple of options.

Within a single SQL statement you can alias table names like so:

SELECT * 
FROM MySuperLongTableName T
WHERE T.SomeField=1

If you need to do this over lots of statements across several scripts a synonym might be a better option:

CREATE SYNONYM SuperT FOR dbo.MySuperLongTableName

edited Sep 23, 2011 at 0:41

Paul Keister

13.1k5 gold badges50 silver badges77 bronze badges

answered Mar 2, 2011 at 22:43

JohnFx

35k19 gold badges108 silver badges169 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cyberherbalist Over a year ago

Neat! However, in my attempt to demonstrate that it does work, I found I needed to put a "for" keyword between the synonym and the object name. As per msdn.microsoft.com/en-us/library/ms177544.aspx. Does it work for you without the "for"? If so, how?

Martin Smith · Accepted Answer · 2011-03-02 22:42:17Z

1

You could create a synonym for that table but obviously you'd need to make sure that nobody changed the definition of the synonym whilst the script was running (and no parallel invocations of the script)

Are you running these in SSMS? If so you could set SQL CMD mode (on the "Query" menu) and use

:setvar tablename "spt_values" 

use master

select * from $(tablename)

answered Mar 2, 2011 at 22:42

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Comments

msarchet · Accepted Answer · 2011-03-02 22:42:53Z

1

You could do this as such:

Declare @TableName As nvarchar(max)
Declare @SQL AS nvarchar(max)
@TableName = 'longtablename'

@SQL = 'Select * From ' + @TableName
EXEC(@SQL)

answered Mar 2, 2011 at 22:42

msarchet

15.3k2 gold badges45 silver badges66 bronze badges

5 Comments

JohnFx Over a year ago

Dynamic SQL, Ugh! This has all sorts of side-effects the OP might not want related to permissions and recompiling query plans.

msarchet Over a year ago

@JohnFx, well he asked for a way to do it, and there honestly isn't anything wrong with Dynamix SQL if you know what you're doing. Also if you're changing tables you are going to have all of those issues anyways.

JohnFx Over a year ago

I didn't catch the part about switching out the table name like a variable. In that case Dynamic SQL might be the best option. However, I still say use it as a last resort.

msarchet Over a year ago

@JohnFx, I'm not saying it's the prettiest way to do things, just probably the simplest

JohnFx Over a year ago

I'm agreeing with you (since my last comment). No need to convince me.

Collectives™ on Stack Overflow

T-SQL Table name alias

3 Answers 3

1 Comment

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related