Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Delete constraints - SQL Server 2005 Expand / Collapse
Author
Message
Posted Saturday, September 22, 2007 1:05 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, February 09, 2014 8:27 PM
Points: 198, Visits: 223
Comments posted to this topic are about the item Delete constraints - SQL Server 2005


Post #401454
Posted Thursday, July 24, 2008 4:08 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 04, 2014 4:40 PM
Points: 751, Visits: 917
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/
Post #540602
Posted Sunday, July 12, 2009 1:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 10:27 AM
Points: 3, Visits: 347
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

Post #751674
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse