Professional Web Applications Themes

Scripting DROP Table *and* DROP (related) Foreign Keys... - Microsoft SQL / MS SQL Server

NEWBIE QUESTION: I'm trying to create a SQL script that I can execute by passing a table name as a parameter, but I'm having a tough time figuring out how to DROP the related Foreign Key Constraint through a variable first since it's in a different table AND goes by a different name. Here's what I have so far (I'm sure there are many other ways to accomplish this too, so any ideas or guidance would be appreciated as well... I'm just starting to learn SQL!): -------- USE Northwind GO DECLARE myTable varchar(255); DECLARE myTable_N varchar(255); DECLARE myTable_basic varchar(255); DECLARE ...

  1. #1

    Default Scripting DROP Table *and* DROP (related) Foreign Keys...

    NEWBIE QUESTION: I'm trying to create a SQL script that I can execute
    by passing a table name as a parameter, but I'm having a tough time
    figuring out how to DROP the related Foreign Key Constraint through a
    variable first since it's in a different table AND goes by a different
    name. Here's what I have so far (I'm sure there are many other ways
    to accomplish this too, so any ideas or guidance would be appreciated
    as well... I'm just starting to learn SQL!):

    --------
    USE Northwind
    GO

    DECLARE myTable varchar(255);
    DECLARE myTable_N varchar(255);
    DECLARE myTable_basic varchar(255);
    DECLARE myTable_Name varchar(255);

    set myTable = '[dbo].[Suppliers]'
    set myTable_N = N'' + myTable;
    set myTable_basic = replace (myTable,'[','')
    set myTable_basic = replace (myTable_basic,']','')
    set myTable_Name = right(myTable_basic,len(myTable_basic)-
    patindex('%.%',myTable_Basic))

    if exists ( SELECT *
    FROM sysobjects
    WHERE (name LIKE '%'+ myTable_Name+ '%') AND (objectproperty(id,
    N'IsForeignKey') = 1)
    )
    ALTER TABLE [dbo].[Products] DROP CONSTRAINT FK_Products_Suppliers

    if exists ( SELECT *
    FROM sysobjects
    WHERE (id = OBJECT_ID(myTable_N)) AND (OBJECTPROPERTY(id,
    OBJECTPROPERTY(id, N'IsUserTable') )= 1))

    DECLARE SQL varchar(255)
    SET SQL = 'DROP table ' + myTable_basic
    EXEC (SQL)
    GO

    --------

    Thanks in advance for any help you can provide,

    Michael
    com
    Michael Guest

  2. #2

    Default Re: Scripting DROP Table *and* DROP (related) Foreign Keys...

    [posted and mailed, please reply in news]

    Michael Zimmerman (com) writes: 

    This query gives you all ALTER TABLE commands you need. You could run
    a cursor over the result set and execute each row. Be sure to make the
    cursor INSENSITIVE.

    SELECT 'ALTER TABLE ' + o2.name + ' DROP CONSTRAINT ' + o3.name
    FROM sysreferences r
    JOIN sysobjects o1 ON o1.id = r.rkeyid
    JOIN sysobjects o2 ON o2.id = r.fkeyid
    JOIN sysobjects o3 ON o3.id = r.constid
    WHERE o1.name = 'accountstats'
    AND o2.name <> 'accountstats'

    The last condition excludes self-referencing constraints.

    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  3. #3

    Default Re: Scripting DROP Table *and* DROP (related) Foreign Keys...

    >> Foreign Key Constraint through a variable first since it's in a
    different table AND goes by a different name. <<

    Do what Erland Sommarskog told yout to solve the immediate problem.
    When you rebuild the tables, be sure that each data element in your
    schema has one and only one name wherever it is used. You will never
    have a usable data dictionary unless you do. Read ISO-11179 for some
    help.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  4. #4

    Default Re: Scripting DROP Table *and* DROP (related) Foreign Keys...

    Michael Zimmerman (com) writes: 

    Didn't I say that you could use a cursor? Here it is:

    DECLARE altertbl_cur INSENSITIVE CURSOR FOR
    SELECT 'ALTER TABLE ' + o2.name + ' DROP CONSTRAINT ' + o3.name
    FROM sysreferences r
    JOIN sysobjects o1 ON o1.id = r.rkeyid
    JOIN sysobjects o2 ON o2.id = r.fkeyid
    JOIN sysobjects o3 ON o3.id = r.constid
    *-> WHERE o1.name = myTable_Name
    *-> AND o2.name <> myTable_Name

    OPEN altertbl_cur

    WHILE 1 = 1
    BEGIN
    FETCH altertbl_cur INTO sql
    IF fetch_status <> 0
    BREAK

    EXEC (sql)
    END

    DEALLOCATE altertbl_cur



    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

Similar Threads

  1. Replies: 4
    Last Post: May 17th, 10:55 PM
  2. 3 Drop Downs Related Problem ...
    By jce1975 in forum Macromedia ColdFusion
    Replies: 1
    Last Post: March 9th, 01:08 AM
  3. Reading Foreign Keys From an Access Table with ADO
    By Bernhard in forum ASP Database
    Replies: 0
    Last Post: September 15th, 08:41 AM
  4. Foreign key constraint against primary keys in two different table.
    By Bill in forum Microsoft SQL / MS SQL Server
    Replies: 7
    Last Post: August 19th, 03:28 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139