How to drop all Default Constraints in a Database?

I need to drop all the default 开发者_运维知识库constraints in our SQL Server 2008 database. Has anyone written a T-SQL script to do this? Note, I just need to drop default constraints, not any other type of constraint.

---

I post the code, because I hate sites that make you register to read the answer. This code is not mine...backup your database first.

from http://www.sqlservercentral.com/Forums/Topic401454-446-1.aspx

USE MyDB

DECLARE @sSql            NVARCHAR(4000) SET @sSql = ''
DECLARE @sTypeDesc       sysname        SET @sTypeDesc = ''
DECLARE @sTableName      sysname        SET @sTableName = ''
DECLARE @sConstraintName sysname        SET @sConstraintName = ''

DECLARE cur CURSOR DYNAMIC FOR
   SELECT name,
          type_desc,
          OBJECT_NAME(parent_object_id)
     FROM sys.objects
    WHERE is_ms_shipped = 0
      AND type_desc LIKE '%_CONSTRAINT'
    ORDER BY type_desc, name

WHILE 1 = 1
BEGIN
   OPEN cur   
   FETCH FIRST FROM cur INTO @sConstraintName, @sTypeDesc, @sTableName

   IF @@FETCH_STATUS != 0
   BEGIN
      CLOSE cur
      BREAK
   END

   WHILE @@FETCH_STATUS = 0
   BEGIN
      BEGIN TRY
         SET @sSql = 'ALTER TABLE ' + QUOTENAME(@sTableName) + ' DROP CONSTRAINT ' + QUOTENAME(@sConstraintName)
         RAISERROR('%s', 10, 1, @sSql) WITH NOWAIT   
         EXEC sp_executesql @sSql
      END TRY
      BEGIN CATCH
         DECLARE @ENumber      INT            SET @ENumber      = ISNULL(ERROR_NUMBER(),          -1)
         DECLARE @ESeverity    INT            SET @ESeverity    = ISNULL(ERROR_SEVERITY(),        -1)
         DECLARE @EState       INT            SET @EState       = ISNULL(ERROR_STATE(),            0) IF @EState = 0 SET @EState = 42
         DECLARE @EProcedure   NVARCHAR(126)  SET @EProcedure   = ISNULL(ERROR_PROCEDURE(), N'{N/A}')
         DECLARE @ELine        INT            SET @ELine        = ISNULL(ERROR_LINE(),            -1)
         DECLARE @EMessageRecv NVARCHAR(2048) SET @EMessageRecv = ISNULL(ERROR_MESSAGE(),        N'')
         DECLARE @EMessageSent NVARCHAR(440)  SET @EMessageSent = N''

         IF ERROR_PROCEDURE() IS NOT NULL   SET @EMessageSent = N'Error %d, Level %d, State %d, Procedure %s, Line %d, Message: '
         SET @EMessageSent = @EMessageSent + ERROR_MESSAGE()
         RAISERROR(@EMessageSent, 10 /* @ESeverity */ , @EState, @ENumber, @ESeverity, @EState, @EProcedure, @ELine) WITH LOG
      END CATCH

      FETCH NEXT FROM cur INTO @sConstraintName, @sTypeDesc, @sTableName
   END

   CLOSE cur
END

DEALLOCATE cur
GO

---

DECLARE @QueryText VARCHAR(MAX)
DECLARE CursorQuery CURSOR FOR

-- get all tables and build dynamic sql
SELECT 

'ALTER TABLE ' + '[' + s.[NAME] + '].[' + t.name + '] DROP CONSTRAINT ['+ c.name + ']'

FROM sys.objects c, sys.objects t, sys.schemas s

WHERE c.type IN ('C', 'F', 'PK', 'UQ', 'D')
        AND c.parent_object_id=t.object_id AND t.type='U' AND t.SCHEMA_ID = s.schema_id
ORDER BY c.type




BEGIN TRY

OPEN CursorQuery

FETCH NEXT FROM CursorQuery INTO @QueryText

WHILE (@@FETCH_STATUS = 0) BEGIN

 PRINT @QueryText 
 EXEC (@QueryText)
 FETCH NEXT FROM CursorQuery INTO @QueryText

END

CLOSE CursorQuery
DEALLOCATE CursorQuery


END TRY



-- ERROR Messaging
BEGIN CATCH

SELECT 
            @QueryText as 'ErrorQuery'
            , ErrorLine = ERROR_LINE()
            ,ErrorNumber = ERROR_NUMBER()
            ,ErrorSeverity = ERROR_SEVERITY()
            ,ErrorState = ERROR_STATE()
            ,ErrorMessage = ERROR_MESSAGE()
            ,ErrorStoredProcedure = ERROR_PROCEDURE()

END CATCH

---

SET NOCOUNT ON
DECLARE @dropstatements TABLE(Script VARCHAR(MAX))
DECLARE @script VARCHAR(MAX)

INSERT INTO @dropstatements
    (Script)
SELECT
    'ALTER TABLE dbo.' + OBJECT_NAME(parent_object_id) + 
    ' DROP CONSTRAINT ' + Name
FROM
    sys.default_constraints

WHILE EXISTS(SELECT 1 FROM @dropstatements)
BEGIN
    SELECT TOP 1 @script = Script FROM @dropstatements ORDER BY Script
    DELETE @dropstatements WHERE Script = @script
    PRINT @script
    EXEC (@script)
END