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

本示例来显示用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*fromcells

/**/ /* ******************修改字段默认值*********************** */
declare @name varchar ( 1000 )
declare @tablename varchar ( 1000 )
declare @fieldname varchar ( 1000 )

set @tablename = ' cells '
set @fieldname = ' AutoPublishCount '
select @name = b.name from syscolumnsa,sysobjectsb
where a.id = object_id ( @tablename )
and b.id = a.cdefault
and a.name = @fieldname
and b.name like ' DF% '
exec ( ' altertablecellsdropconstraint ' + @name )

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

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