Post reply

best way to update isnullable and default values for all columns of a given data type

  • April 20, 2004 at 1:22 pm

    #84900

    Tried searching for a script for the best way to update isnullable and default values for all columns of a given data type but couldn't find one.  I have some decimal and money fields that currently allow nulls and do not specify a default value.  I would like script the altering of all of these fields to not allow nulls and set the default value to '0.0' or '0'.

    TIA,

    Scott

  • April 20, 2004 at 2:24 pm

    #503543

    Enterprise Manager can create this script for you.  

    1.  In Enterprise Manager, Right Click a Table that you wish to Alter and choose Design Table (The Design Table tablename window opens)...
    2. Click on the Column that you wish to change/add a default value for.
    3. In the Columns Properties box at the bottom left of the window, edit the Default Value text box.
    4. Next, Uncheck the Allow Nulls Checkbox for the columns that you do not want to Allow Nulls for.
    5. Click the Save Change Script button on the toolbar (The save change script dialog box appears with the Alter Script)
    6. From here you can Click the YES button to save the script to a file, or select the text and Copy to the Clipboard.
    7. Use this script, especially the ALTER Statement to edit for your other tables.

    Goodluck,

    Pete

  • April 20, 2004 at 2:40 pm

    #503549

    Well, Pete's recommendation (thanks for the reply) did work for generating a rather large and complex script that would work specifically for the Table and all of its dependents (keys, constraints, etc.).  But I'm no SQL expert and I doubt I could modify this script to the generalized level to operate on all tables in the db.

    Any chance someone out there has a general script that would work for all tables (including their constraints, keys, etc.) in a given db?

    TIA,

    Scott

Viewing 3 posts - 1 through 2 (of 2 total)

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