SQL Clone

<a href="//g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="//g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

Log in :: Register :: Not logged in

Write for us

<a href="//g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="//g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Generate DDL script to drop table constraints

By VPSD Gupta, 2013/05/24

Ever had the need to quickly execute a T-SQL script and generate SQL statements in SQL Server 2008 to drop a primary key, foreign keys, check constraints and/or defaults from a table ... Here is one script that would help you do this. You can pass few parameters and the script would generate all the required data with the alter statements. Create the SP in your database and you can run the SP using the following signature:

-- Generate SQL Statement to DROP the Primary Key of a table

EXEC usp_Drop_Table_Constraints @sConstraint_Type = 'PK', @sDB_Name = 'MY_DATABASE', @sTable_Name = 'MY_TABLE'

-- Generate SQL Statement to DROP the Foreign Keys of a table

EXEC usp_Drop_Table_Constraints @sConstraint_Type = 'FK', @sDB_Name = 'MY_DATABASE', @sTable_Name = 'MY_TABLE'

-- Generate SQL Statement to DROP the Check Constraints of a table

EXEC usp_Drop_Table_Constraints @sConstraint_Type = 'CK', @sDB_Name = 'MY_DATABASE', @sTable_Name = 'MY_TABLE'

-- Generate SQL Statement to DROP the Default Constraints of a table

EXEC usp_Drop_Table_Constraints @sConstraint_Type = 'DF', @sDB_Name = 'MY_DATABASE', @sTable_Name = 'MY_TABLE'

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Drop_Table_Constraints]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_Drop_Table_Constraints]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_Drop_Table_Constraints]
    (
        @sConstraint_Type    VARCHAR(50),        -- PK, FK, CK, DF
        @sDB_Name            SYSNAME,
        @sTable_Name        SYSNAME,
        @sColumn_Name        SYSNAME = NULL
    )
AS
BEGIN
    SET NOCOUNT ON
    
    DECLARE    @sSQL            VARCHAR(8000),
            @sStr            VARCHAR(1000)
    
    CREATE TABLE #Temp (
        Type                VARCHAR(50),
        DBName                SYSNAME,
        Schema_Name            SYSNAME,
        Table_Name            SYSNAME,
        Column_Name            SYSNAME DEFAULT '',
        Constraint_Name        SYSNAME,
        DROP_SQL            VARCHAR(8000)
    )

    -------------------------------------------------------------
    
    IF @sConstraint_Type = 'PK'
    BEGIN
        -- Drop PK
        SET @sSQL = '    
                        SELECT    DISTINCT Type        = ''PK'',
                                DBName                = ''' + @sDB_Name + ''',
                                Schema_Name            = s.name,
                                Table_Name            = p.name,
                                PK_Constraint_Name    = o.name,
                                DROP_SQL            = ''ALTER TABLE ' + @sDB_Name + '.'' + s.Name + ''.'' + c.Table_Name + '' DROP CONSTRAINT '' + c.CONSTRAINT_NAME
                        FROM    ' + @sDB_Name + '.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c
                                INNER JOIN ' + @sDB_Name + '.sys.objects o ON c.CONSTRAINT_NAME = o.name
                                INNER JOIN ' + @sDB_Name + '.sys.objects p on o.parent_object_id = p.object_id
                                INNER JOIN ' + @sDB_Name + '.sys.schemas s ON p.schema_id = s.schema_id
                        WHERE    c.TABLE_NAME = ''' + @sTable_Name + '''
                        AND        o.type = ''PK''
                    '
                    
        INSERT INTO #Temp (Type, DBName, Schema_Name, Table_Name, Constraint_Name, DROP_SQL)
        EXEC (@sSQL)
    END

    -------------------------------------------------------------

    ELSE IF @sConstraint_Type = 'FK'
    BEGIN
        -- Drop FK
        SET @sSQL = '
                        SELECT    DISTINCT Type        = ''FK'',
                                DBName                = ''' + @sDB_Name + ''',
                                Schema_Name            = s.name,
                                Table_Name            = p.name,
                                FK_Constraint_Name    = o.name,
                                DROP_SQL            = ''ALTER TABLE ' + @sDB_Name + '.'' + s.Name + ''.'' + c.Table_Name + '' DROP CONSTRAINT '' + c.CONSTRAINT_NAME
                        FROM    ' + @sDB_Name + '.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c
                                INNER JOIN ' + @sDB_Name + '.sys.objects o ON c.CONSTRAINT_NAME = o.name
                                INNER JOIN ' + @sDB_Name + '.sys.objects p on o.parent_object_id = p.object_id
                                INNER JOIN ' + @sDB_Name + '.sys.schemas s ON p.schema_id = s.schema_id
                        WHERE    c.TABLE_NAME = ''' + @sTable_Name + '''
                        AND        o.type = ''F''
                    '

        INSERT INTO #Temp (Type, DBName, Schema_Name, Table_Name, Constraint_Name, DROP_SQL)
        EXEC (@sSQL)
    END

    -------------------------------------------------------------
    
    ELSE IF @sConstraint_Type = 'CK'
    BEGIN
        -- Drop Check Constraint
        SET @sSQL = '    
                        SELECT    DISTINCT Type        = ''CK'',
                                DBName                = ''' + @sDB_Name + ''',
                                Schema_Name            = s.name,
                                Table_Name            = p.name,
                                Column_Name            = c.column_name,
                                CK_Constraint_Name    = o.name,
                                DROP_SQL            = ''ALTER TABLE ' + @sDB_Name + '.'' + s.Name + ''.'' + c.Table_Name + '' DROP CONSTRAINT '' + c.CONSTRAINT_NAME
                        FROM    ' + @sDB_Name + '.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c
                                INNER JOIN ' + @sDB_Name + '.sys.objects o ON c.CONSTRAINT_NAME = o.name
                                INNER JOIN ' + @sDB_Name + '.sys.objects p on o.parent_object_id = p.object_id
                                INNER JOIN ' + @sDB_Name + '.sys.schemas s ON p.schema_id = s.schema_id
                        WHERE    c.TABLE_NAME = ''' + @sTable_Name + '''
                        AND        o.type = ''C''
                    '
        INSERT INTO #Temp (Type, DBName, Schema_Name, Table_Name, Column_Name, Constraint_Name, DROP_SQL)
        EXEC (@sSQL)
    END

    -------------------------------------------------------------

    ELSE IF @sConstraint_Type = 'DF'
    BEGIN
        -- Drop Default Constraint
        SET @sSQL = '
                        SELECT    DISTINCT Type        = ''DF'',
                                DBName                = ''' + @sDB_Name + ''',
                                Schema_Name            = s.name,
                                Table_Name            = p.name,
                                Column_Name            = c.name,
                                DF_Constraint_Name    = o.name,
                                DROP_SQL            = ''ALTER TABLE ' + @sDB_Name + '..'' + p.name + '' DROP CONSTRAINT '' + o.name 
                        FROM    ' + @sDB_Name + '.sys.columns c 
                                INNER JOIN ' + @sDB_Name + '.sys.objects o on c.default_object_id = o.object_id
                                INNER JOIN ' + @sDB_Name + '.sys.objects p on o.parent_object_id = p.object_id
                                INNER JOIN ' + @sDB_Name + '.sys.schemas s ON p.schema_id = s.schema_id
                        WHERE    o.type = ''D'' 
                        AND        c.object_id = object_id(''' + @sDB_Name + '..' + @sTable_Name + ''')
                    '
        INSERT INTO #Temp (Type, DBName, Schema_Name, Table_Name, Column_Name, Constraint_Name, DROP_SQL)
        EXEC (@sSQL)
    END

    -------------------------------------------------------------
    
    SELECT    *
    FROM    #Temp

    -------------------------------------------------------------
    
END

/*

EXEC usp_Drop_Table_Constraints @sConstraint_Type = 'PK', @sDB_Name = 'MY_DATABASE', @sTable_Name = 'MY_TABLE'    -- Drop PK
EXEC usp_Drop_Table_Constraints @sConstraint_Type = 'FK', @sDB_Name = 'MY_DATABASE', @sTable_Name = 'MY_TABLE'    -- Drop FKs
EXEC usp_Drop_Table_Constraints @sConstraint_Type = 'CK', @sDB_Name = 'MY_DATABASE', @sTable_Name = 'MY_TABLE'    -- Drop Check Constraints
EXEC usp_Drop_Table_Constraints @sConstraint_Type = 'DF', @sDB_Name = 'MY_DATABASE', @sTable_Name = 'MY_TABLE'    -- Drop Default Constraints

*/
GO

Total article views: 2599 | Views in the last 30 days: 2

Related Articles

SCRIPT

Generate DDL script to create table and all constraints

2012/10/16

Generate all DDL statements for a given table (Create Table, PK, FK, Check Constraints, Defaults)

FORUM

Check Constraint with Case Statement

2010/08/06

Check Constraint with Case Statement

ARTICLE

Beware to the System Generated Constraint Name

2007/10/02

New columnist Gregory Larsen used to not care what his objects were called until recently when he be...

ARTICLE

Generating Insert Statements

2010/02/02

This article from new author Oleg Netchaev describes the cursor-less script used to generate insert ...

SCRIPT

MERGE Statement Generator

2012/06/05

Generates the merge statement for a given table.

Contribute