数据库使用SQL查询删除修改约束、获取数据库表默认值

1、查出某字段的约束名称

SELECT c.name FROM sysconstraints a 
INNER JOIN syscolumns b on a.colid=b.colid 
INNER JOIN sysobjects c on a.constid=c.id 
WHERE a.id=object_id('表名') 
AND b.name='字段名' 

2、删除某字段的约束

ALTER TABLE 表名 DROP CONSTRAINT 约束名称

3、修改默认值约束

ALTER TABLE 表名 ADD CONSTRAINT 约束名称 DEFAULT(1) FOR 字段

4、获取数据库表的默认值

SELECT SO.NAME AS "表名", SC.NAME AS "字段名", SM.TEXT AS "默认值"
FROM dbo.sysobjects SO INNER JOIN dbo.syscolumns SC ON SO.id = SC.id
LEFT JOIN dbo.syscomments SM ON SC.cdefault = SM.id
WHERE SO.xtype = 'U' and so.name='表名'
ORDER BY SO.[name], SC.colid