How to Rename a Table and Column Names in SQL Server

SQL Server SP_RENAME Stored Procedure to Rename Table and Column Names
We can use SP_RENAME storage procedure to rename a table in MS SQL Server.
Some of the relational database management system (RDBMS) does not support this command, because this is not standardizing statement.

Example:

CREATE TABLE TblProduct

(     

       Pid     INT,

       P_Name   NVARCHAR(50)

)

GO

Now we can rename the Table Name and column names as below:

Renaming the  Table TblProduct to Table_Products :

EXEC SP_RENAME 'TblProduct', 'Table_Products'

GO

Renaming Column Pid to ProductID :

EXEC SP_RENAME  'Table_Products.Pid', 'ProductID', 'COLUMN'

GO

Renaming Column P_Name to ProductName :

EXEC SP_RENAME  'Table_Products.P_Name', 'ProductName', 'COLUMN'

GO

EXEC Command:

EXEC Command is used to execute stored procedures. Here in this example we used the Stored Procedure called ‘SP_RENAME’.

Example

EXEC sp_help 
GO

EXECUTE sp_help 
GO

EXEC command executes stored procedure where as EXEC() function takes dynamic string as input and executes them.

EXEC('EXEC sp_help').

Stored Procedure:

If a repetitive T-SQL task has to be executed within an application, then the best repository for it is a program called a stored procedure, stored in SQL Server. Storing the code inside the SQL Server object gives us many advantages, like: 

· Security due to encryption
· Performance gains due to compilation
· Being able to hold the code in a central repository:
· Altering the code in SQL Server without replicating in several different programs
· Being able to keep statistics on the code to keep it optimized
· Reduction in the amount of data passed over a network by keeping the code on the server
· Hiding the raw data by allowing only stored procedures to gain access to the data You may have executed some ad-hoc queries for tasks like inserting data, querying information in other systems, or creating new database objects such as tables.
All these tasks can be placed within a stored procedure, so that any developer can run the same code without having to recreate the T- SQL commands. Also, generalizing the code for all values makes it generic and reusable.

GO Command :

GO is not a Transact-SQL statement; it is often used in T-SQL code. Go causes all the statements from the beginning of the script or the last GO statement (whichever is closer) to be compiled into one execution plan and sent to the server independent of any other batches. 

SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad-hoc session or script if this is the first GO.G

O Statement must be written in new line as it is not T-SQL command. T-SQL statement can not occupy the same line as GO. GO statement can contain comments.

Example for SQL SERVER 2005 for database AdventureWorks.

USE AdventureWorks; 
GO

DECLARE @MyMsg VARCHAR(50) 

SELECT @MyMsg = 'Hello, World.'
GO 
PRINT @MyMsg
---- @MyMsg is not valid after this GO ends the batch. —- Yields an error because @MyMsg not declared in this batch.
GO

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence Professional

--------------------------------------------------------------------------------------------------------