SQL Server Performance Forum – Threads Archive
How to ALter a Default for a column?
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__<TABLE NAME>__<<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ARTIAL COLUMN NAME></i><br /><br />but the column name is most likely partial.<br />For example, DF__Location__descr__0F975522,<br />for table Location and column description,<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 <br /><br />DEFAULT ‘0’ ,</i>instead of<br /><br />[description] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL<br /><br />and follwed by an alter table statement and with a given default name:<br /><br /><i>ALTER TABLE [dbo].[<Table>] WITH NOCHECK ADD <br />CONSTRAINT [DF_<Table>_description] DEFAULT (‘0’) FOR [description]…</i><br /><br />I tried the following but it has a syntax error<br />near constraint:<br /><br /><b>ALTER TABLE [dbo].[myTABLE] <br />ALTER COLUMN [myColumn] [varchar] (50)<br />CONSTRAINT [DF_myTABLE_myColumn] <br />DEFAULT (‘NA’) WITH VALUES</b><br /><br /><u>Maybe SQL Server doesn’t support this kind of operation</u>?<br /><br />If there is a way to drop the DEFAULT on this column or<br />the entire table, is a working solution too.<br /><br />Thanks.<br /><br />mingus<br />You have to first drop the constraint with:
ALTER TABLE [dbo].[myTABLE]
DROP CONSTRAINT [DF_myTABLE_myColumn] And then re-add it with: ALTER TABLE [dbo].[myTABLE]
ADD CONSTRAINT [DF_myTABLE_myColumn]
DEFAULT (‘NA’)
FOR [myColumn]
Thanks, but I think I need a function to
return the exact name of old DF_ since there is
no sure way to identify it – someone else
could name it DF__myTable__myColumnJS, or
the default could be
DF__partialTableName__partialColumnName__otherIdentifier Or, if we could do it without identifying the
exact name of DF: ALTER TABLE [dbo].[myTABLE]
DROP CONSTRAINT ON/FOR column [myColumn] (it doesn’t work)
I found a not so pretty solution by using: select a.name from dbo.sysobjects a join dbo.syscolumns b on
(a.xtype = ‘D’ and b.name = <Column_name> and a.id = b.cdefault and a.name like ‘%_<Table_name>_%’)
]]>