Post reply

Renaming tables in Enterprise Manager

  • June 21, 2005 at 2:25 pm

    #87769

    I'm using SQLServer 2000 and on occasion use Enterprise Manager to rename a table or a view (either right click on the object in the tables/views list and select rename (or just hit F2 and rename)). The problem I experience is that later down the road, I need to send someone else the appropriate SQL for the table/view. I use Enterprise Manager's Generate SQL Script... command (right click on table/view - All Taks - Generate SQL Script) and the generated script contains the old name. This is not much of a problem when I do just a table or two (visual check), but there are times when I need to do this with hundreds of tables/views. Is there any way to tell what existing tables/views underlying name (used in the SQL generation) does not match with the actual SQL name? How do I avoid this in the future?

    Thanks for any help!

  • June 21, 2005 at 2:35 pm

    #567245

    if you have a view named view1 and you rename it with EM (or with sp_rename), the script will still go as :

    CREATE VIEW dbo.view1...

    You have to change that too when you rename the objects.

    As to find the mismatches, this can help but it's really not 100% :

    Select O.name, O.XType from dbo.SysComments C inner join dbo.SysObjects O on C.id = O.id where C.text not like '%' + O.name + '%' and colid = 1 and O.XType not in ('C', 'D', 'S') order by O.Xtype

  • June 21, 2005 at 2:55 pm

    #567258

    I usually don't attempt renaming any objects as I have quite a few dependencies on most of them (usually stored procedures)..

    However, I cannot recreate what you posted - I just renamed a test table via EM and generated a script and the script had the new name not the old one....







    **ASCII stupid question, get a stupid ANSI !!!**

  • June 21, 2005 at 2:57 pm

    #567259

    It happens only for objects that require a script to work (views/functions/sps...)

  • June 21, 2005 at 3:09 pm

    #567266

    that makes sense - tscott mentioned tables so I only tried it on a test table...

    however, that has to be one of the scariest things I've seen - renaming an object and getting everything out of sync...

    tscott - I'm curious - why are you renaming your db objects ?! Specially so many of them as you indicate ?!







    **ASCII stupid question, get a stupid ANSI !!!**

  • June 21, 2005 at 3:11 pm

    #567267

    That's another great question .

    That's also another great reason to have a formal code implementation method in place.

  • June 22, 2005 at 4:38 am

    #567341

    Well, I suppose tscott renames table this_month_sales to previous_month_sales or something equally horrible otherwise I can hardly imagine why anyone would need to rename hundreds of objects.

  • June 22, 2005 at 6:43 am

    #567379

    Can't either...

    Unless he has Cust1Orders, Cust2Orders...

  • June 22, 2005 at 6:45 am

    #567383

    Maybe the manager just thought of a naming convention to standardise everything in their database & didn't realise the consequences ?!?!







    **ASCII stupid question, get a stupid ANSI !!!**

  • June 22, 2005 at 7:17 am

    #567416

    Now that would be a first... a non technical superior guy that thinks he knows how things should work. Never seen that happen before .

  • June 22, 2005 at 12:53 pm

    #567630

    Finally - a chance to speak. I am not renaming hundreds of tables or views. Quite often, I design a series of new tables/views for additional functionality we are incorporating into our programs. During the design phase (on a test server), I change names in order to truly reflect their functionality (especially views). After completing the design and testing, I use Enterprise Manager to create the scripts for me - running them on our live data servers. This is where I first started seeing the errors (and after further review, it only occurs on the views).

    Most of my data is very year specific and we have programs designed for each year's data (tax software). Each year, I must create new tables that are ususally very close (but not close enough to re-use or add a year field) to the previous year's table. I use Enterprise Manager to generate SQL creation scripts and then modify each table as appropriate. If I didn't know which scripts were messed up, it makes for a boatload of pain. I guess it also leads to another question, is there a different tool someone would use for creating the SQL scripts from the existing data?

    I found all the rename musings quite interesting. Personally, I was thinking renaming every table to something nonsensical in order to maintain job security (that was probably the next post).

    Thanks for all the replies - I uncovered three bad views (thought there would be more).

  • June 22, 2005 at 12:58 pm

    #567636

    Alright... can't help you much there.

    You can use EM to script all the objects you want.

  • June 22, 2005 at 1:08 pm

    #567641

    tscott - I'm still a little confused - if you're designing new tables and views each (tax) year to tweak something - why aren't you just "creating" instead of "renaming" ??

    For my part, I have all my db objects scripted and stored in version control - anytime I need to make an alteration I open the script in QA - make the changes and then run it - then I save the changes back as my next version etc...

     

     







    **ASCII stupid question, get a stupid ANSI !!!**

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply