SQL Server Performance

ALTER DEFAULT, How?

Discussion in 'General Developer Questions' started by mtmingus, Jun 18, 2004.

  1. mtmingus New Member

    How to change the default on a column from, for<br />example, '0', to 'NA'?<br /><br />dbo.sysobjects (where XTYPE = 'D') <br />tells you there is a default for this column:<br /><br /><i>DF__&lt;TABLE NAME&gt;__&lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />ARTIAL COLUMN NAME&gt;</i><br /><br />but the column name is most likely partial.<br />For example, <i>DF__Location__descr__0F975522</i>,<br />for table <i>Location </i>and column <i>description</i>,<br />when the original script for the table create contains<br />something like:<br /><br /><i>[description] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT '0' ,</i><br /><br />instead of<br /><br /><i>[description] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL</i><br />and follwed by an alter table statement and with a given default name:<br /><br /><i>ALTER TABLE [dbo].[&lt;Table&gt;] WITH NOCHECK ADD <br />CONSTRAINT [DF_&lt;Table&gt;_description] DEFAULT ('0') FOR [description]</i>...<br /><br /><br />Thanks.<br /><br />-mingus
  2. Luis Martin Moderator

    How about using Enterprise Manager in table desing?



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  3. mtmingus New Member

    I'd like to provide a scripting tool for the
    customers, instead of asking them to learn
    SQL Server.

    The issue is, there is an existing default
    on this column and I like to replace it with
    something different. I cannot drop
    this column and re-create it with this new
    default. But if there is a way to drop the
    old DEFAULT, that will work too, I could
    simply ADD the new DEFAULT.

    I tried the following but it has a syntax error
    near constraint:

    ALTER TABLE [dbo].[myTABLE]
    ALTER COLUMN [myColumn] [varchar] (50)
    CONSTRAINT [DF_myTABLE_myColumn]
    DEFAULT ('NA') WITH VALUES


    mingus
  4. Luis Martin Moderator

    Please Moderators, close this post. I was reopen in General DBA Questions.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

Share This Page