T-SQL Table name alias

问题:

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.

回答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)

回答2:

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

回答3:

You could do this as such:

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

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