How to rename a column using SQL Script.

Problem: Renaming a column or table by writing a SQL Query

Solution :

sp_rename , 

Starting SQL Server 2008 or higher.

Changes the name of a user-created object in the current database. This object can be a table, index, column, alias data type, or Microsoft .NET Framework common language runtime (CLR) user-defined type.

Example :

As always let’s do a small a demo to rename column from Test_id to TestID_old in Test.Test

I am going to check whether a column exist in a table and schema then I am going to rename a column using SP_RENAME and then specifying the column name I would like to choose for the existing column.

Please see the sample script below:

IF EXISTS (

                                SELECT 1

                                FROM INFORMATION_SCHEMA.COLUMNS

                                WHERE table_name = N'Test'

                                                AND table_schema = N'Test'

                                                AND column_name = N'Test_id'

                     )

BEGIN

               

                EXEC sp_rename '[Test].[Test].[Test_id]'

                                ,'TestID_Old'

                                ,'COLUMN';

                PRINT N'Test_id Column in [Test].[Test] has been altered to TestID_Old'

END

 Please see the screenshot below to rename a column: