CODEWITHSUNDEEP
sqlsql-server-2008t-sql
icecurtain
icecurtain

Reputation: 675

Explicitly constraint drop not using the name

CREATE TABLE [dbo].[tmp_rg_xx_LBJ]
(
[ROWVERSION] [bigint] NULL,
[ROWDATE] [datetime] NULL,
[SAPNO] [int] NOT NULL DEFAULT 1)


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

GO

I have a script that creates a table with a default in, but I want to drop the constraint. No problem manually as I know the name, except I want drop the constraint after recreating the table. So I can just re run the drop constraint script. How do you do this without explicitly using the name?

Upvotes: 1

Views: 406

Answers (2)

QuilleyPowers
QuilleyPowers

Reputation: 110

Below is something I came up with to create drop statements for implicit constraints. You can just kill the where statement and it should do the job. It also generates the create statements, but I may have flaws for composite constraints. One of my projects uses SSDT and if a column is getting edited and the same script is cascaded to other environments the implicit constraint drop will blow up, so below also handles that scenario.

SELECT distinct /*obj_table.NAME      AS 'table', 
        isnull(columns.NAME,col.name)        AS 'column',
        obj_Constraint.NAME AS 'constraint',
        obj_Constraint.type AS 'type',
        sss.name as 'schema',*/
        'ALTER TABLE [' + ltrim(rtrim(sss.name))+'].['+ltrim(rtrim(obj_table.name)) + '] DROP CONSTRAINT [' + obj_Constraint.NAME + '];' As 'Wrong_Implicit_Constraint',
        'ALTER TABLE [' + ltrim(rtrim(sss.name))+'].['+ltrim(rtrim(obj_table.name)) + '] ADD CONSTRAINT [' + CASE obj_Constraint.type 
        WHEN 'D' THEN 'DF' WHEN 'F' THEN 'FK' 
        WHEN 'UQ' THEN 'UQ' WHEN 'PK' THEN 'PK' WHEN 'N' THEN 'NN' WHEN 'C' THEN 'CK' 
        END + '_' + ltrim(rtrim(obj_table.name)) + '_' + isnull(columns.NAME,col.name) + ']' +
        CASE obj_Constraint.type WHEN 'D' THEN ' DEFAULT (' + dc.definition +') FOR [' + columns.NAME + ']'
        WHEN 'C' THEN ' CHECK (' + cc.definition +')'
        WHEN 'PK' THEN ' PRIMARY KEY (' + col.name +')'
        WHEN 'UQ' THEN ' UNIQUE (' + col.name +')'
        ELSE '' END +
        ';' As 'Right_Explicit_Constraint'
        --select * 
    FROM   sys.objects obj_table 
        JOIN sys.objects obj_Constraint ON obj_table.object_id = obj_Constraint.parent_object_id 
        JOIN sys.sysconstraints constraints ON constraints.constid = obj_Constraint.object_id 
        LEFT JOIN sys.columns columns ON columns.object_id = obj_table.object_id 
            AND columns.column_id = constraints.colid 
            Left join sys.indexes ind   on ind.object_id = obj_table.object_id
            Left join sys.index_columns indcol   on indcol.object_id = obj_table.object_id
            Left join sys.columns col   on col.object_id = obj_table.object_id
                and col.column_id = indcol.column_id
        left join sys.schemas sss on obj_Constraint.schema_id=sss.schema_id 
        left join sys.default_constraints dc on dc.object_id = obj_Constraint.object_id
        left join sys.check_constraints cc on cc.object_id = obj_Constraint.object_id
    WHERE obj_Constraint.type_desc LIKE '%CONSTRAINT'
    AND obj_table.type not in ('TF')
    AND ind.type_desc not in ('NONCLUSTERED')
    AND RIGHT(obj_Constraint.name,8) LIKE '%[A-Z]%'          --Ensure alpha in last 8
    AND RIGHT(obj_Constraint.name,8) LIKE '%[0-9]%'          --Ensure numeric in last 8
    AND RIGHT(obj_Constraint.name,8) not LIKE '%[^0-9A-Z]%'  --Ensure no special chars
    AND (RIGHT(obj_Constraint.name,10) LIKE '[_][_]________' OR RIGHT(obj_Constraint.name,18) LIKE '[_][_]________________')  --match double underscore + 8 chars of anything *Edit 16 for pk in OR

Upvotes: 0

marc_s
marc_s

Reputation: 754478

You can try this - this will look up the necessary info in the system catalog views and create a "drop" T-SQL command that you can copy & execute to get rid of your system-named default constraint:

SELECT
    DropCmd = 'ALTER TABLE ' + t.name + ' DROP CONSTRAINT ' + df.Name
FROM sys.default_constraints df
INNER JOIN sys.columns c ON df.parent_object_id = c.object_id AND df.parent_column_id = c.column_id
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE 
    t.Name = 'YourTableNameHere'
    AND c.name = 'YourColumnNameHere'

If you need to put this into your own script - you can assign the DropCmd to a variable and then execute that SQL statement using dynamic SQL:

DECLARE @DropStatement NVARCHAR(200)

SELECT
    @DropStatement = N'ALTER TABLE ' + t.name + N' DROP CONSTRAINT ' + df.Name
FROM sys.default_constraints df
INNER JOIN sys.columns c ON df.parent_object_id = c.object_id AND df.parent_column_id = c.column_id
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE 
    t.Name = 'YourTableName'
    AND c.name = 'YourColumnName'

EXEC sp_executesql @DropStatement

Upvotes: 1

Related Questions