| Author |
Topic |
|
ricky_newbee
Posting Yak Master
126 Posts |
 Posted - 2005-01-28 : 08:19:00
|
| All, Suggestions welcome please. If i use sp_rename to rename a column in table, do i have to change the column name referenced in other tables and hard coded sql in stored procedures? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-01-28 : 09:10:08
|
| If you want it to reference the new column name then yes.If you don't restart the system then the queries may still work for a while until the query plan is recalculated.You will probably get an error if you try to rename a column that has a foreign key dependancy.==========================================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. |
 |
|
|
ricky_newbee
Posting Yak Master
126 Posts |
Posted - 2005-01-28 : 09:17:12
|
| Thanks! But how about Stored Procedures? Do i have to change them too or restarting the server would take care of it? I am sorry for the questions. I am little confused. Bare with me. |
|
|
|
TimS
Posting Yak Master
198 Posts |
Posted - 2005-01-28 : 18:46:31
|
| Re-starting the server should cause it to break right away.Tim S |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-01-28 : 20:33:09
|
You have to change each of the stored procedures. There's a lot of work to changing the name of a column. Normally, changing the name implies you're changing its usage. This is normally a bad idea and a result of faulty design. You shouldn't ever use a column for different things as the database evolves, or becomes corrupt as the case may be.  MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
ricky_newbee
Posting Yak Master
126 Posts |
Posted - 2005-01-31 : 13:51:30
|
| Thanks Derrick. Changing Column is the only option we have right now. The database i picked up was from 6.5 going to 2000. Most of the tables are using SQL Server 2000 reserved keywords as column names. I proposed an option using delimited identifiers where reserved keywords exists. But application team wants to change the column name instead.... |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-01-31 : 15:02:37
|
| Script all the SPs and do a search and replace - hope the column name isn't something like 'it'.You'll have to do the same for fk's, indexes, triggers.==========================================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. |
|
|
|
ricky_newbee
Posting Yak Master
126 Posts |
Posted - 2005-01-31 : 15:17:38
|
| Yeah that's what my idea was. Thanks.BTW. I have another question which is not related to this issue, i have a table with about 10 million rows. Table1 with col1,col2,col3 ...... Is there any way to jumble columns? Meaning col2,col1,col3. |
|
|
|
AndyB13
Aged Yak Warrior
583 Posts |
Posted - 2005-01-31 : 16:18:52
|
| Do you mean change the order in the table, i would say that wouldnt be good practice as the table would need to be recreated, indexes, constraints etc reapplied just to move the columns about.Use a view or sproc SELECT col2,col1,col3 FROM Table1.Also if there is another view or sproc like thisSELECT col1,col2,col3 FROM Table1ORDER BY 2 --not recommended but SQL does accept thisThen this would change to ordering by Col1 after you moved the column orderAndy |
|
|
|
ricky_newbee
Posting Yak Master
126 Posts |
Posted - 2005-02-01 : 13:32:16
|
| Thanks Andy!! I am going to keep this as my primary option. Thanks again. |
|
|
|
|
SP_RENAME