Using a variable in ALTER TABLE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Using a variable in ALTER TABLE

Author

Topic

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-12 : 12:26:33

I have a lot of tables that in need to change their names to a tempory name then back to the original name. I thought I could do this by using a variable in place of the table name. But I get the following error. What is the correct syntax or proper way to do this?

DECLARE @SourceFile varchar(100
SET @SourceFile = 'SourceFile'

ALTER TABLE @SourceFile RENAME 'XSourceFile'

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '@SourceFile'.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-12 : 12:33:38

Need dynamic sql for that like:

exec ('ALTER TABLE ' + @SourceFile + ' RENAME '''XSourceFile''')

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-12 : 12:43:26

I just did some more playing with the command an found

ALTER TABLE 'SourceFile' RENAME 'XSourceFile'

does not work. And I don't see a RENAME clause for ALTER TABLE. Am I missing something?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-12 : 13:07:31

Tried sp_rename?

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-12 : 13:14:07

Just finished trying that. it works

EXEC sp_rename 'ReferenceFile', 'XReferenceFile'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources