How to rename table name into alternative name in sql server 2005

Posted by Kirubakaranb.Tech under Sql Server on 1/22/2013 | Points: 10 | Views : 2075 | Status : [Member] | Replies : 4
I try this two coding but not work properly

Rename the login table to newlogin

ALTER TABLE login RENAME TO newlogin


reply correct answer.




Responses

Posted by: Kundan64 on: 1/22/2013 [Member] Starter | Points: 25

Up
0
Down
SP_RENAME login, newlogin

above query will give Caution: Changing any part of an object name could break scripts and stored procedures.
but your table name will be changed.

Mark as Answer if it helps you.


Kirubakaranb.Tech, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Prashikudupi on: 1/23/2013 [Member] Starter | Points: 25

Up
0
Down
In addition to above reply,

SP_RENAME can also be used to rename a column name of an object.

EXEC SP_RENAME 'TABLE.OLD_COL ', 'NEW_COL ','COLUMN'


Kirubakaranb.Tech, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pavanandey on: 1/23/2013 [Member] Bronze | Points: 25

Up
0
Down
The script for renaming any column : sp_RENAME 'TableName.[OldColumnName]' , '[NewColumnName]', 'COLUMN'

The script for renaming any object (table, sp etc) : sp_RENAME '[OldTableName]' , '[NewTableName]'

Mark answer if this helps you

Thanks
Pavan Kumar
Mark Answer if this fits the need

Kirubakaranb.Tech, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Satyapriyanayak on: 1/23/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
Renaming database table column to new name.
Renaming database table to new name.

In both the cases we will first see existing table. Rename the object. Test object again with new name.

1. Renaming database table column to new name.

Example uses AdventureWorks database. A small table with name "Table_ First" is created. Table has two fields ID and Name.

Now, to change the Column Name from "Name" to "NameChange" we can use command:

USE AdventureWorks
GO
sp_RENAME 'Table_First.Name', 'NameChange' , 'COLUMN'
GO

Following Fig. show use of SP_RENAME Command

You can see the column name "Name" is now changed to "NameChange".

USE AdventureWorks
GO
SELECT *
FROM Table_First
GO

Following fig. verify that the column name has been changed.

2.Renaming database table to new name.

We can change the table name too with the same command.

sp_RENAME 'Table_First', 'Table_Last'
GO

Following fig. Shows how we can change Table Name.

Now, the table name "Table_First" is renamed as "Table_Last".

"Table_First" will no longer be available in database. We can verify this by running script:

USE AdventureWorks
GO
SELECT *
FROM Table_First
GO

The Messages shows an error "Invalid object name 'Table_First'."

To check that the new renamed table exist in database run script:

USE AdventureWorks
GO
SELECT *
FROM Table_Last
GO

If this post helps you mark it as answer
Thanks

Kirubakaranb.Tech, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response