Delete constraints - SQL Server 2005

• sqldummy79

  SSC-Addicted

  Points: 491

  More actions

  September 22, 2007 at 1:05 pm

  #198042

  Comments posted to this topic are about the item Delete constraints - SQL Server 2005

• TimothyAWiseman

  SSCrazy Eights

  Points: 8819

  More actions

  July 24, 2008 at 4:08 pm

  #848133

  Thank you. This saved me the trouble of writing something like it.

  Incidentally, it does not get rid of defaults if you consider those constraints.

  ---
  Timothy A Wiseman
  SQL Blog: http://timothyawiseman.wordpress.com/

• Larry Leonard-399461

  SSC Veteran

  Points: 228

  More actions

  July 12, 2009 at 1:46 am

  #1023577

  Try this:

  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 FIRST FROM cur INTO @sConstraintName, @sTypeDesc, @sTableName

  END

  CLOSE cur

  END

  DEALLOCATE cur

  GO