column rename

[jrprogr]

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.

 

[ptheriault]

You can rename your column with this syntax.

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.

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

- Paul
- If at first you don't succeed, find out if the loser gets anything.