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
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
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
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
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.)
5条答案
按热度按时间1cklez4t1#
One solution from a search : (Edited for Default constraints)
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 :
wxclj1h53#
Script posted by gbn does not work for me, so I'm using a modified version:
x4shl7ld4#
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.)