sql-server – 如何删除表上的所有默认约束约束

发布时间：2020-12-12 07:00:10 所属栏目：MsSql教程来源：网络整理

导读：如何在SQL 2005中删除属于特定表的所有默认约束？解决方法一个解决方案 from a search :(编辑为默认约束) SET NOCOUNT ONDECLARE @constraintname SYSNAME,@objectid int,@sqlcmd VARCHAR(1024)DECLARE CONSTRAINTSCURSOR CURSOR FORSELECT NAME,object_idF

如何在SQL 2005中删除属于特定表的所有默认约束？

解决方法

一个解决方案 from a search :(编辑为默认约束)

SET NOCOUNT ON

DECLARE  @constraintname SYSNAME,@objectid int,@sqlcmd         VARCHAR(1024)

DECLARE CONSTRAINTSCURSOR CURSOR  FOR
SELECT NAME,object_id
FROM   SYS.OBJECTS
WHERE  TYPE = 'D' AND @objectid = OBJECT_ID('Mytable')

OPEN CONSTRAINTSCURSOR

FETCH NEXT FROM CONSTRAINTSCURSOR
INTO @constraintname,@objectid

WHILE (@@FETCH_STATUS = 0)
BEGIN
    SELECT @sqlcmd = 'ALTER TABLE ' + OBJECT_NAME(@objectid) + ' DROP CONSTRAINT ' + @constraintname
    EXEC( @sqlcmd)
    FETCH NEXT FROM CONSTRAINTSCURSOR
    INTO @constraintname,@objectid
END

CLOSE CONSTRAINTSCURSOR
DEALLOCATE CONSTRAINTSCURSOR

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!