Thursday, 19 July 2018

How To Rename A Column Name Or Table Name In SQL Server


I have seen a lot of times beginners seeking for a request to rename either the column name or table name in SQL Server. For some reason, the tables and columns created in the production servers cannot be dropped and recreated with the custom structure as the prior table could be consisted of data. In this case, we can either use the stored procedure or use the GUI to rename either the Table name or Column name.

Let’s create the tables and insert some data into the table.

CREATE TABLE [dbo].[SQLArena](
       [Column1] [nvarchar](300) NULL,
       [Column2] [nvarchar](300) NULL
) ON [PRIMARY]

INSERT INTO SQLArena(column1, column2) values(1,'value1')

SELECT * FROM SQLArena






Case 1: Using the GUI to rename the Table Name or Column Name

Open the object explorer, and expand the respective database. Under the respective database, expand once again the table and columns that need to be edited. To rename the table name or column name, a single left mouse key button on the table name or the column name turns to an edit box. Here, you will be able to rename to the desired name needed. Let’s look at the same. 
TableName Change:






ColumnName Change:





Case 2 : Using the system stored procedure to rename the Table Name or Column Name


Format for TableName Change:  EXEC sp_RENAME '[OldTabName]' , '[NewTabName]'


EXEC sp_RENAME '[SQLArena]' , '[SQLArena_TEST]'

Format for ColumnName Change: EXEC sp_RENAME 'TableName.OldColName' , 'NewColName', 'COLUMN'

EXEC sp_RENAME 'SQLArena.Column1' , 'ID', 'COLUMN'
EXEC sp_RENAME 'SQLArena.Column2' , 'Value', 'COLUMN'

Doing the system stored procedure to change the Table Name or Column Name can pop a warning message in the result window, which is not to be considered a major concern.

Warning message: “Caution: Changing any part of an object name could break scripts and stored procedures.

0 comments: