Renaming active objects? - 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)

Renaming active objects?

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2005-12-06 : 14:57:58

Me again. Big web app; about 250 tables, 800 SP's.

Like many applications that have grown over time, this one started out with terrbile-to-nonexistant naming conventions. Particularly for tables.

I'd like to address that, but I'm not sure how. For any given table, there are many SP's that access it, most of which are in use and being used by customers.

I can't just work on new versions of the SP's, copy the tables, and then update the app, because by the time I'm done with the copy, there will be new data that doesn't want to be lost.

It seems to me that the most elegant approach to this would be if SQL server supported cname/symlink type functionality: the ability to refer to a single table with multiple names. Then I could add the new, proper name to a table, update the SP's one-by-one, and then remove the old name from the table. Somehow, I don't think SQL server supports that. Does it?

Any other suggestions? Or am I just stuck with an app with some really embarassing table names?

Thanks
-b

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2005-12-06 : 15:11:30

If this is a 24/7 no downtime kind of operation then you are stuck with the table names. But who really cares what the table names are, the user sees what the application presents and table names don't have to be fancy or match any naming convention. Is the problem that the user now needs to do reporting on their own or something so they will see the table list?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-06 : 15:13:33

Don't rename the tables!
By far the safest and simplest thing is to live with the bad names.

However, one approach would be to rename the tables but create views to new tables with the old, bad table names as the view name. Then when you have ALL your code pointing to the new tables you should be able to drop the views.

Personally, I'd live with the bad names and take that lesson learned to the next project.
Don't rename the tables!

EDIT:
btw, Don't rename the columns either! I'm sure you're quite happy with your column names right? columns like: totvenaccbal and bus213.

Be One with the Optimizer
TG

aiken
Aged Yak Warrior

525 Posts

Posted - 2005-12-06 : 15:44:20

The problem is that the names are confusing the developers (including me), to the point where it's an issue.

For instance, our proper naming convention is something like userRecords for a table of records about a user, and users_records for a join table that connects the users table to the records table. Older tables are named things like user_records, which isn't a join table, or even worse, things like records_per_user or current_user_records (*shudder* -- was there a time when the "user_records" table had records that weren't current? Or what?).

It's annoying, and it makes writing, reading, and debugging SP's difficult because joins get confusing (you think you're joining against a join table, but it's actually a data table, or vice versa). Part of the problem is that we've learned what we're doing and *new* stuff is named properly, so working on old stuff leads to all sorts of (incorrect) assumptions and aggravation, since the naming conventions work 90% of the time.

Column names are OK so far; tables only have <10 columns, in general, so finding a column is easy, and they are at least not actively misleading in their names

.

I like the view approach; I'll try it out on a few of the less-used and most-annoying tables and see how it goes. It seems to me that that trick would be to write a SP that would (quickly) do the rename/view creation; does that sound right?

Cheers
-b

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-06 : 16:08:12

Don't rename the tables!

>>It seems to me that that trick would be to write a SP that would (quickly) do the rename/view creation; does that sound right?

I've never actually done this, but here is the approach I'd take. Note the transaction around the rename table / create View:


use northwind
set nocount on

--create some test objects
create table junk (i int)
go
insert junk (i) values (1)
go
create proc junk_sel as select i from junk
go

--perform the deed
begin tran
	exec sp_rename junk, newjunk
	exec('create view junk as select i from newjunk')
commit
go

--see if the proc still works now that it is accessing a view
exec junk_sel

go

alter proc junk_sel as select i from newJunk
drop view junk
go
exec junk_sel

go
--clean up
drop proc junk_sel
drop table newjunk

Don't rename the tables!

Be One with the Optimizer
TG

aiken
Aged Yak Warrior

525 Posts

Posted - 2005-12-06 : 19:37:32

Thanks for the help and ideas. I'll play with it some and I'll be sure to report when you can say "told ya so."

Cheers
-b

Kristen
Test

22859 Posts

Posted - 2005-12-06 : 19:57:15

We had to do this a long time ago. I made a couple of tables called MigrateTables and MigrateColumns which basically had the Old and New names in it. I used this to "trial" the new names until they were right, and then create a bunch of RENAME Columns and Tables commands, AND create VIEWs (to the new tables/columns) in place of the old tables.

Our table/column names are unique within the application, so we can use SED to globally change them (in all source code for SProcs etc.) if we need to change them again.

But we do occasionally still wind up with tables called things like 'MyTable' and 'MyTable_V2' when the first design turned out to be Complete Crapola!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources