I am using SQL Server's own table drop script (right click on table, script as etc).

Posted on 2011-05-10
Last Modified: 2012-05-11
I am using SQL Server's own table drop script (right click on table, script as etc).

When I run the drop script for a constraint, it does not drop the constraint.
Question by:Mr_Shaw
    LVL 22

    Accepted Solution

    I'm not sure I understand your question. Did you use the drop table script and then replace the name of the table with the name of the constraint?

    The drop constraint script would look like this:
    IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[constraintName]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblName]'))
    ALTER TABLE [dbo].[tblName] DROP CONSTRAINT [constraintName]

    Open in new window

    Can you provide the script that you use and that is not working?

    Author Comment

    sorry... question badly written.

    The following script returns NULL.

    select OBJECT_ID(N'default_value_constraint')

    Therefore SQL Servers 'SCRIPT AS' script does not work....

    IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'default_value_constraint') AND type = 'D')
    ALTER TABLE [dbo].[tb1] DROP CONSTRAINT [default_value_constraint]

    LVL 22

    Assisted Solution

    by:Nico Bontenbal
    When you run:
    SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'default_value_constraint') 

    Open in new window

    what does it return?

    Author Closing Comment


    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now