Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Posts
    1

    Unanswered: Changing data type

    Hi all,

    I have to extend the length of a field, which appears in hundred of tables in a database, and maybe involved in constraints/index/primary key. It is difficult for me to drop all the constraints/index/primary key before altering each table one by one.....Is there any easier way to drop all constraints and keys (instead of dropping them one by one)? Or any better (faster) way to extend the length of this field?

    Thanks for any help here!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK, here's one method:

    Script out your entire database. Then use search and replace to modify all instances of the field length (character, I assume?). Then use this script to create a new, corrected database, and use DTS to transfer data from the old database.

    I hope you are aware, though, that you may likely render much of your SQL code (procedures, functions, triggers...) obsolete if they reference your field and attempt to assign it or concatenate it to variables that are of insufficient length. You may have quite a debugging job ahead of you. A 3rd party package such as ERWIN might be of assistance.

    blindman

  3. #3
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Just generate ALTER TABLE statements:

    SELECT 'ALTER TABLE ' + T.Name + ' ALTER COLUMN ' + C.Name + ' nVarChar(<YourNewFieldLength>);'
    FROM SysColumns C INNER JOIN
    SysObjects T ON T.id = C.id
    WHERE T.XType = 'U' AND T.Name <> 'dtproperties' AND
    C.Name = < YourColumnName >

    You can consider to open a cursor, and to execute your statement dynamically. Alternatively, you can also generate a script.

    I didn't check it for constraints, but indices are automatically updated by this statement.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •