How to remove all default restrictions in a database?

Question

I need to drop all default restrictions in our SQL Server 2008 database. Has anyone written a T-SQL script for this? Please note: I just need to discard the default restrictions, and not for any other type of restrictions.

Answer 1

I am posting the code because I hate sites that force you to register to read the answer. This code is not mine ... first back up your database.

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 

Answer 2

 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 

Answer 3

 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