Post reply

Dropping Constraints from multiple tables

  • November 13, 2012 at 11:20 am

    #270890

    I want to drop a bunch of constraints on tables that have similar names, by generating alter statements from the information schema, i have the query below and i cant figure out why i cant do this

    SELECT 'ALTER TABLE ' +

    t.Table_Name +

    ' drop column ' +

    c.constraint_name +

    FROM Information_Schema.tables t

    INNER JOIN Information_Schema.constraint_name C

    ON t.Table_name = c.Table_name

    WHERE c.constraint_name LIKE '%DF_abc%'

    can information schema have something like this does it have a special value for constraints as it does for columns

  • November 13, 2012 at 11:29 am

    #1558587

    instead of dropping and recreating, it would be a lot easier to simply rename the existing constraints instead. sp_rename takes any object name that exists in sys.objects , not just tables or columns.

    see this thread , where i posted a code example on the same issue, where someone wanted to drop/recreate default constraints with a naming convention:

    http://www.sqlservercentral.com/Forums/Topic1382812-392-1.aspx#bm1383093

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 13, 2012 at 11:41 am

    #1558597

    I have to drop the column after i drop the contraint, unless i can force a column drop that have binding values somehow

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply