rename - 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

SQL Server 2000 Forums

SQL Server Development (2000)

rename

Author

Topic

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-02-28 : 10:56:41

I want to rename the column name of the table which contains data and also i want to check and replace the column name in the stored procedures(50), views(20) and some triggers. Please suggests me the best way to replace it .

One way is to generate the scripts and find & replace( CTRL H)with old columnname to newcolumn in the script file.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-28 : 11:04:24

"One way is to generate the scripts and find & replace( CTRL H)with old columnname to newcolumn in the script file."
You have to go through these steps don't think there is any other better way.

1. script out your stored procedure, views, triggers, find and replace old name with new name
2. rename the column name using sp_rename
3. re-apply your stored procedures etc
4. check any application that reference the old column name and change that also.

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-03-01 : 09:58:59

KH

Below is also better way of identifying the stored procedures columns..

EXEC sp_rename
@objname = 'table.column',
@newname = 'NewName',
@objtype = 'COLUMN'

You can use the information schema view to find where it's used. For Example I use this one to find column referrences in stored procedures.

CODE
select *
from INFORMATION_SCHEMA.ROUTINES --INFORMATION_SCHEMA.ROUTINE_COLUMNS
where ROUTINE_TYPE='PROCEDURE'
AND SPECIFIC_NAME like 'usp%'
AND ROUTINE_DEFINITION like '%MyColumn%'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources