SQL Server How can I drop all the default constraints constraints on a table

fwzugrvs  于 2023-08-02  发布在  其他
关注(0)|答案(5)|浏览(116)

How can I drop all the default constraints belonging to a particular table in SQL 2005?

1cklez4t

1cklez4t1#

One solution from a search : (Edited for Default constraints)

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
eqqqjvef

eqqqjvef2#

I know this is old, but I just found it when googling. A solution that works for me in SQL 2008 (not sure about 2005) without resorting to cursors is below :

declare @sql nvarchar(max)

set @sql = ''

select @sql = @sql + 'alter table YourTable drop constraint ' + name  + ';'
from sys.default_constraints 
where parent_object_id = object_id('YourTable')
AND type = 'D'

exec sp_executesql @sql
wxclj1h5

wxclj1h53#

Script posted by gbn does not work for me, so I'm using a modified version:

SET NOCOUNT ON

DECLARE  @DfId INT, @TableId INT,
         @SqlCmd    VARCHAR(1024)

DECLARE DFCONSTRAINTCUR CURSOR FOR
SELECT [parent_object_id] TABLE_ID, [object_id] DF_ID
FROM   SYS.OBJECTS
where parent_object_id = OBJECT_ID('<table name>') 
and [TYPE] = 'D' 

OPEN DFCONSTRAINTCUR

FETCH NEXT FROM DFCONSTRAINTCUR
INTO @TableId, @DfId

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

CLOSE DFCONSTRAINTCUR
DEALLOCATE DFCONSTRAINTCUR
x4shl7ld

x4shl7ld4#

Declare @TableName VarChar(Max), @ConstraintName VarChar(Max), @SqlQuery nVarChar(Max)
Declare Cursor1 Cursor Local For Select ST.[name] AS "Table Name", SD.[name] AS "Constraint Name"
FROM sys.tables ST Inner Join sys.syscolumns SC ON ST.[object_id] = SC.[id] 
Inner Join sys.default_constraints SD ON ST.[object_id] = SD.[parent_object_id] And SC.colid = SD.parent_column_id 
ORDER BY ST.[name], SC.colid
    Open Cursor1 Fetch Next From Cursor1 Into @TableName, @ConstraintName
        While @@Fetch_Status = 0 Begin
            Set @SqlQuery = 'Alter Table ' + @TableName + ' Drop Constraint ' + @ConstraintName
            Print @SqlQuery
            Execute (@SqlQuery)
            Fetch Next From Cursor1 Into @TableName, @ConstraintName
        End
    Close Cursor1;
Deallocate Cursor1;
GO
xqnpmsa8

xqnpmsa85#

Just why do you want to do this? Dropping constraints is a pretty drastic action and affects all users not just your process. Maybe your problem can be solved some other way. If you aren't the dba of the system, you should think very hard about whether you should do this. (Of course in most systems, a dba wouldn't allow anyone else the permissions to do such a thing.)

相关问题