用SQL语句修改字段的默认值

本示例来显示用SQL语句修改cells表的字段AutoPublishCount的默认值从10改为1000

/**/ /*******************创建表结构和默认值************************/
IF   EXISTS  ( SELECT  name  FROM  master.dbo.sysdatabases  WHERE  name  =  N ' testdb ' )
     DROP   DATABASE   [ testdb ]
go

create   database  testdb
go

use  testdb
go

CREATE   TABLE   [ Cells ]  (
     [ CellID ]   [ int ]   IDENTITY  ( 1 ,  1 )  NOT   NULL  ,
     [ PageID ]   [ int ]   NULL  ,
     [ AutoPublishCount ]   [ int ]   NOT   NULL   CONSTRAINT   [ DF_Publish_AutoPublishCount ]   DEFAULT  ( 10 ),
     CONSTRAINT   [ PK_CELLS ]   PRIMARY   KEY    CLUSTERED
    (
         [ CellID ]
    )  WITH    FILLFACTOR   =   90    ON   [ PRIMARY ]
)  ON   [ PRIMARY ]
GO

/**/ /*************************************************/
--  -- select * from cells

/**/ /*******************修改字段默认值************************/
declare     @name     varchar ( 1000 ) 
declare     @tablename     varchar ( 1000 ) 
declare     @fieldname     varchar ( 1000 ) 
  
set   @tablename = ' cells '
set   @fieldname = ' AutoPublishCount '
select     @name = b.name    from    syscolumns   a,sysobjects   b 
  where    a.id = object_id ( @tablename )  
and    b.id = a.cdefault  
and    a.name = @fieldname  
  and    b.name    like     ' DF% '  
exec ( ' alter   table   cells   drop   constraint    ' + @name )

ALTER   TABLE   [ cells ]
ADD   CONSTRAINT  DF_Publish_AutoPublishCount  DEFAULT  ( 1000 )   FOR   [ AutoPublishCount ]

/**/ /*******************************************************************/

转载于:https://www.cnblogs.com/downmoon/archive/2007/12/29/1019807.html