[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

drop constraints that belong to a columnname in any table

there is a particular column name that is in many tables in a database. (aduit field - bit type).

it's constraint need to be dropped from all tables.

so
tabl1.AuditStatus
tabl2.AuditStatus
tabl3.AuditStatus
tabl4.AuditStatus
tabl5.AuditStatus

all have constraints.. all should be dropped through a script in one round.. how do you do that?
0
25112
Asked:
25112
  • 2
2 Solutions
 
25112Author Commented:
the constraint it default.. the AudiStatus is by default 0. That default constraint should be dropped for all columns called AuditStatus (if it has the default constraint)
0
 
Scott PletcherSenior DBACommented:
USE <your_db_name>

DECLARE @sql varchar(max)

SELECT @sql =
    (SELECT 'ALTER TABLE [' + OBJECT_NAME(df.parent_object_id) + '] DROP CONSTRAINT [' + df.name + '];~~' --~~ used as placeholder for CHAR(13) + CHAR(10) because of XML
    FROM sys.default_constraints df
    INNER JOIN sys.columns c ON
        c.object_id = df.parent_object_id AND
        c.column_id = df.parent_column_id
    --WHERE
        --c.name = 'AuditStatus'
    ORDER BY
        1
    FOR XML PATH('')
    )
   
SET @sql = REPLACE(@sql, '~~', CHAR(13) + CHAR(10))

SELECT @sql AS [--sql]

-- be sure to verify commands generated before running them :-)
--EXEC(@sql)
0
 
Anthony PerkinsCommented:
This will list all the DEFAULT CONSTRAINTS:
SELECT  t.name,
        dc.name
FROM    sys.tables t
        INNER JOIN sys.columns c ON t.object_id = c.object_id
        INNER JOIN sys.default_constraints dc ON t.object_id = dc.parent_object_id
                                                 AND c.column_id = dc.parent_column_id
WHERE   c.name = 'AudiStatus'

Open in new window


This will create the script to drop them (assuming there are no SCHEMAS:
SELECT  'ALTER TABLE [' + t.name + '] DROP CONSTRAINT [' + dc.name +']'
FROM    sys.tables t
        INNER JOIN sys.columns c ON t.object_id = c.object_id
        INNER JOIN sys.default_constraints dc ON t.object_id = dc.parent_object_id
                                                 AND c.column_id = dc.parent_column_id
WHERE   c.name = 'AuditStatus'

Open in new window

0
 
25112Author Commented:
very nice
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now