Check if Constraint Exists before dropping

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Check if Constraint Exists before dropping

Author

Topic

Looper
Yak Posting Veteran

68 Posts

Posted - 2011-06-01 : 04:46:30

I want to check if a constraint exists before attempting to drop it, so that my script is re-runable and does not throw up error messages when the constraint does not exist.

I have tried the following:


IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_ED_FCA]') AND parent_object_id = OBJECT_ID(N'[dbo].[ED]'))
			BEGIN
				ALTER TABLE [dbo].[ED] 
					DROP CONSTRAINT [DF_ED_FCA]
			END

IF (SELECT COALESCE(COL_LENGTH('ED','FCA'),0)) <> 0
   BEGIN
      ALTER TABLE Test.dbo.ED
         DROP COLUMN FCA
   END
ELSE
   PRINT '**** ED.FCA does not exist ****'
GO

When i run this i get the following error messages
Msg 5074, Level 16, State 1, Line 20
The object 'DF_ED_FCA' is dependent on column 'FCA'.
Msg 4922, Level 16, State 9, Line 20
ALTER TABLE DROP COLUMN FCA failed because one or more objects access this column.

Looper
Yak Posting Veteran

68 Posts

Posted - 2011-06-01 : 05:27:39

I figured it out instead of sys.check_constraints I needed to use sys.objects

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources