We help IT Professionals succeed at work.

Create dynamic stored procedure for rename

Medium Priority
187 Views
Last Modified: 2016-05-18
Hello,

Have this query for renaming two columns

EXEC sp_RENAME '[dbo].[Corr].[UID]' , Tablename _UID, 'COLUMN'

Need dynamic stored procedure any suggestions

Inputs:
Tablename
Old columnname= UID
new column= tablename_UID

Cheers
Comment
Watch Question

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Since you have the code, why do you need this encapsulized in a dynamic stored procedure?
Smells like homework.

Author

Commented:
Trying something like



 EXEC sp_RENAME @TableName +'.[UID]' , @TableName +'_UID' , 'COLUMN'
              

Cant get it to work
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Try something like this.  I don't have my SSMS in front of me so you might have the play with the 's to get it perfect.  Also don't prefix a custom stored procedure with sp_ as SQL will at first assume it's a system stored procedure, which will make it run longer

CREATE PROC rename_sp (@TableName nvarchar(100)) AS

Declare @sql nvarchar(1000) 

@sql = 'EXEC sp_RENAME ''' + @TableName + '.[UID]'== + ' , ''' + @TableName _UID + ''', '''COLUMN'

-- Run this a couple of times and eyeball to make sure it works. 
SELECT @sql
GO

Open in new window

Author

Commented:
Syntax errors
IT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
I'm not sure why do you need a dynamic SP since it's already a dynamic SP. So reinventing the wheel:
CREATE PROCEDURE MySp_rename @OldName VARCHAR(128), @NewName VARCHAR(128)
AS
    EXEC sp_rename @OldName, @NewName, 'COLUMN'
GO

Open in new window

Author

Commented:
Cheers,
where is the parameter for table name for this procedure
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
The sp_rename doesn't have a table name parameter. When you give the @oldName you need to give it as 'tableName.ColumnName' so it will know immediately the table that you're referring to.