InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 02:15:38 GMT20http://www.sqlservercentral.com/Forums/Topic401454-446-1.aspxTry this:[code]USE MyDBDECLARE @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, nameWHILE 1 = 1BEGIN 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 FIRST FROM cur INTO @sConstraintName, @sTypeDesc, @sTableName END CLOSE curENDDEALLOCATE curGO[/code]Sun, 12 Jul 2009 01:46:21 GMTLarry Leonard-399461http://www.sqlservercentral.com/Forums/Topic401454-446-1.aspxThank you. This saved me the trouble of writing something like it.Incidentally, it does not get rid of defaults if you consider those constraints.Thu, 24 Jul 2008 16:08:47 GMTtimothyawisemanhttp://www.sqlservercentral.com/Forums/Topic401454-446-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/Miscellaneous/31937/">Delete constraints - SQL Server 2005</A>[/B]Sat, 22 Sep 2007 13:05:35 GMTsqldummy79