Renaming Columns. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Renaming Columns.

Author

Topic

potchakri
Starting Member

10 Posts

Posted - 2007-08-08 : 09:30:06

Hi all,

I need to rename the Columns of a table in SQL Server 2005.
The table is consisting of nearly 200 columns and the requirement is that every column needs to be appended with a name that would be given by me.

Example:

Let the name of the table be "Applicationdata"
let it contain columns say

Name
DOB
Gender
Place
Qualification

now I need to add a code say "Appl" before every column so that after the code is run, the column names sholud be changed as

Appl_Name
Appl_DOB
Appl_Gender
Appl_Place
Appl_Qualification.

Do we have any procedure like that, if yes, please post it.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-08-08 : 10:06:38

You can use the sp_rename stored procedure.

You can read about it in SQL Server Books Online.

CODO ERGO SUM

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-08 : 11:39:03

You can use dynamic sql and update all the columns for the table in a loop. Check for sys.columns join with sys.objects and filter by table name to get list of all columns.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

pootle_flump

1064 Posts

Posted - 2007-08-08 : 12:23:12

Remember that anything & everything SQL wise that refers to these columns will be broken once you do this too. You might want to get a handle on the scale of this before you do it (assumng you haven't already).

potchakri
Starting Member

10 Posts

Posted - 2007-08-09 : 02:53:18

Hi all,

Thank you for the replies. I am trying to work on that presently.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2007-08-10 : 07:41:47

--Create the procedure and call the procedure for each table.The below procedure takes tablename as first param & Prefix for column as second param.

Create PROCEDURE RenameColumns(@TableName as varchar (50), @Prefix AS varchar(50))
as
BEGIN
DECLARE @S varchar(8000)
select @S = isnull(@S,'')+ '
exec sp_rename ''' +@TableName +'.' + name +''',''' + @Prefix + '_'+ name + ''';
'
FROM syscolumns
WHERE id = (select id FROM sysobjects WHERE xtype='U' and name= @TableName)

EXECUTE (@S)

End

GO
--Call the procedure
EXEC RenameColumns 'Cust2','PD'

potchakri
Starting Member

10 Posts

Posted - 2007-08-14 : 04:12:56

Thank you soo much all of you.

It was a great help. Thanks a lot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources