Post reply

numeric to uniqueidentifier

  • June 21, 2008 at 12:19 am

    #123428

    Hi friends,

    i need to change the data type from numeric to uniqueidentifier.

    i have a tables domain, location and visit.

    table - domain (domain_id, name,creator,date)

    table - location (location_id, location_name, domain_id, date)

    table - visit(visit_id, domain_id,location_id)

    there are foriegnkey relations on these tables. i need to change the data type for the ids(domain_id,location_id and visit_id) from the numceric to uniqueidentifier.

    is there any script to change the data type. i can;t change it manually because i am having totally 150 tables and having the relations most of the tables.

    i need a script to change the data type.

    thanks in advance.

  • June 21, 2008 at 12:23 am

    #832170

    No, I don't believe such a script exists... you'll need to write one. And, yeah, it's going to take a bit of time.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 21, 2008 at 10:11 am

    #832240

    You can change it manually, it would take time, but it's probably the best way to do this. If it's not every table, and a common pattern, a script could miss something.

    Which means lots of testing.

    Which is silly, since this is probably a one time thing. If this is for a bunch of clients, all having this database, write a script and test it. Or better yet, just go through SSMS, change each table, get the script (save that), and then put them all together.

    150 tables is some work, but it's not something that will take weeks. Likely a few hours.

  • June 21, 2008 at 12:05 pm

    #832260

    ramu.guggilla (6/21/2008)

    i have a tables domain, location and visit.

    table - domain (domain_id, name,creator,date)

    table - location (location_id, location_name, domain_id, date)

    table - visit(visit_id, domain_id,location_id)

    there are foriegnkey relations on these tables. i need to change the data type for the ids(domain_id,location_id and visit_id) from the numceric to uniqueidentifier.

    Here's how I would go about this for, say, the domain table:

    1) Find all Foreign Keys with domain_id in them. Script them and then drop them.

    2) In SSMS, open the table designer and change domain_id from numeric datatype to VARBINARY. Save the table. Now change it again to UNIQUEIDENTIFER and save it again.

    3) Use INFORMATION_SCHEMA.COLUMNS to find all tables that reference domain_id.

    4) For every table found in (3) repeat the steps in (2), changing the datatype of domain_id first to VARBINARY (saving) and then to UNIQUEIDENTIFIER.

    5) Reapply the Foreign Keys using the script from (1).

    Make sure that you have a good backup of your DB first, because this is a non-reversible process.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • June 22, 2008 at 10:26 am

    #832382

    It is also possible to automate this process, though that is a bit of work in itself.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • June 22, 2008 at 9:00 pm

    #832439

    please let me know how to automate this process.

  • June 24, 2008 at 5:56 am

    #833251

    Upon further consideration, I think that I may have spoken too soon. It turns out that these are difficult elements to script out from the SQL command line. I might have something in a week or two, but it'll probably involve custom CLR.

    Sorry.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 7 posts - 1 through 6 (of 6 total)

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