| Author |
Topic |
|
heze
Posting Yak Master
192 Posts |
 Posted - 2007-06-14 : 07:50:56
|
| hi I want to use sp_rename to rename columns only, iver researched some in internet and got a little concerned for the warnings as well as the warning you get when you run rename the "breaking script" thing, however, most of the secondary effects I read applied to renaming sprocs and views but not columns, to be "safe" i did----------------------------------------alter table add columnupdate new column with original column valuesalter table drop original column----------------------------------------the problem with this approach is not only that I had to bundle each statement in an exec dynamic sql statement because the compiler was complaining about the "updating" of a column that does not exist, but in addition since it is a considerable number of columns I want to rename, the code is getting larger and larger when instead i could be solving my problem using one line of codeexec myDb.sp_rename 'mytable.mycolumnOriginalName','myName','column'----------my question is: are there any counter indications for using sp_rename for this case?thank you |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-06-14 : 08:10:27
|
| What about Procs/Functions/Views using old column name? They will no longer work.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
heze
Posting Yak Master
192 Posts |
Posted - 2007-06-14 : 08:14:07
|
| h,in this case, Im using the sp_rename in a script that creates a table, so in other words, the immediate second next step in the entire system after te table is produced, is the renamin of this field, the immediate first next step is the resaon for which Im having to rename, so no procedure, function or view is ever using the original name... |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-06-14 : 08:17:39
|
| If there is nothing in between creating table and then renaming column, why don't you create table with desired column name in the first place? Why create table with wrong column name and then correct it?Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-14 : 08:24:02
|
| What you were doing could be a really bad ideaseehttp://www.nigelrivett.net/SQLAdmin/AlterTableProblems.htmlyou could have done it byalter table add columngoupdate new column with original column valuesgoalter table drop original columngoto save the dynamic sql.The issue with renaming is that sql server executes plans which use the object IDs - if the plan is not recreated then it could use an old object id - but you aren't changing any IDs here - columns are referenced via tableid + colid and the data via the offset in the data row - none of which you are changing.To be safer you can sp_recompile the table. Even better is to restart the server.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
heze
Posting Yak Master
192 Posts |
Posted - 2007-06-14 : 08:24:44
|
| h,im updating 50 columns in a fact table as the intermediate step, im using a while cycle and I need a sepcific naming convention for only those 50 columns, if i name my "to tename" field using the same naming convention as those 50 fields, it will also be affected by the update and I dont want that, I need the originalname when the automatic update runs and then I need to change the name so that it follows the same convention as the 50 fields mentioned |
|
|
|
heze
Posting Yak Master
192 Posts |
Posted - 2007-06-14 : 08:26:18
|
| nr,my alter/update statements are within a stored procedure and "go" was not allowed, |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-14 : 08:46:06
|
| Hold the table name, column name, logical column name in a table.Then you can crate the table with the correct column name but use this metadata table to use the logical name in code (actually sounds like this just needs to be a flag) so the columns you don't want to perform operations on can be ignored.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
heze
Posting Yak Master
192 Posts |
Posted - 2007-06-14 : 08:51:26
|
| nr, youre right can do that, I actually have everything set up to do it that way, thanks |
|
|
|
|
safe to use sp_rename?