34,587
社区成员
发帖
与我相关
我的任务
分享
DECLARE @cname VARCHAR(1000)
--取得约束对象的名字
SELECT @cname=dc.name
FROM sys.default_constraints AS dc INNER JOIN sys.[columns] AS c ON c.column_id=dc.parent_column_id
WHERE OBJECT_NAME(c.[object_id])='TestTable' AND c.name='b'
SELECT @cname
IF LEN(@cname)>0 exec('ALTER TABLE TestTable DROP CONSTRAINT '+@cname)
ALTER TABLE TestTable ALTER COLUMN b INT
IF LEN(@cname)>0 exec('ALTER TABLE dbo.TestTable ADD CONSTRAINT '+@cname+' DEFAULT 0 FOR b')
CREATE TABLE TestTable(ID INT IDENTITY(1,1) PRIMARY KEY,b BIT)
GO
INSERT INTO TestTable (b)
SELECT 0
GO 10
ALTER TABLE TestTable ALTER COLUMN b int ---直接修改类型
SELECT * FROM TestTable AS tt