Results 1 to 10 of 10
  1. #1
    Join Date
    May 2003
    Posts
    369

    Unanswered: How to drop all PKs on tables in database?

    I have a list of 35 tables that need to drop the primary key index from in my database.

    My problem is as follows for these 35 tables:

    1. How can I get a list of all the primary keys for this subset of tables in my database
    2. How can I drop just the PK for each of these tables?

    I want an easy quick way to do this without having to manually do this for each of the 35 tables in my database. I dont want to do this for all tables just the subset.

    Thanks

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    Code:
    declare @Table varchar(100)
    declare @PK varchar(100)
    declare cur cursor for select name,object_name(parent_obj) from sysobjects where xtype='PK' and object_name(parent_obj) in ('table1','table2',......)
    open cur
    fetch next from cur into  @PK, @Table
    while @@fetch_status = 0
    begin
    	exec ('alter table ' + @Table + ' drop constraint ' + @PK )
    	fetch next from cur into  @PK, @Table
    end
    close cur
    deallocate cur

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I think it would be safer to do this inside the loop:

    print 'alter table ' + @Table + ' drop constraint ' + @PK

    rather than this:

    exec ('alter table ' + @Table + ' drop constraint ' + @PK )

    that way, you can inspect the result for correctness, make sure you really want to execute it, etc.

    when playing the sql-from-sql game, you should execute only after inspecting the result, IMO.

  4. #4
    Join Date
    Aug 2006
    Posts
    16
    the answer mention above would goes wrong if foreign keys are there so we need to drop all foreing keys first then apply above mention procedure
    1 step first
    drop all foreign keys relation ship
    declare @Table varchar(100)
    declare @FK varchar(100)
    declare cur cursor for select name,object_name(parent_obj) from sysobjects where xtype='F' and object_name(parent_obj) in ('Table1','Table2',....)
    open cur
    fetch next from cur into @FK, @Table
    while @@fetch_status = 0
    begin
    exec ('alter table ' + @Table + ' drop constraint ' + @FK )
    fetch next from cur into @FK, @Table
    end
    close cur
    deallocate cur
    2. step second now drop all primary key relation ship
    declare @Table varchar(100)
    declare @PK varchar(100)
    declare cur cursor for select name,object_name(parent_obj) from sysobjects where xtype='PK' and object_name(parent_obj) in ('table1','table2',......)
    open cur
    fetch next from cur into @PK, @Table
    while @@fetch_status = 0
    begin
    exec ('alter table ' + @Table + ' drop constraint ' + @PK )
    fetch next from cur into @PK, @Table
    end
    close cur
    deallocate cur
    Last edited by greenindia; 02-02-07 at 07:44.

  5. #5
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    right... we need to drop the FKs first.
    but the code from greenindia is having the following problems

    1) xtype for foriegn-key is "F" and not "FK"
    2) who says that the tables with FKs belongs to the same set of that with PKs? since the same set of tables are used in the code
    object_name(parent_obj) in ('Table1','Table2',....)

    u need a trip from sysforeignkeys to trap the relation properly
    Last edited by upalsen; 02-02-07 at 06:22.

  6. #6
    Join Date
    Aug 2006
    Posts
    16
    you are my dear friend upalsen , xtype for foreign key should have been F instead of FK.

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    anybody think of asking our friend why he would want to do this before we hand him a loaded gun?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by Thrasymachus
    anybody think of asking our friend why he would want to do this before we hand him a loaded gun?
    What would be the fun in that?
    Inspiration Through Fermentation

  9. #9
    Join Date
    May 2003
    Posts
    369

    Question on script

    Hi all,

    Thanks for your help, however I cannot see the output of the print commands in SQL Server Query Analyzer when I run the cursor script:

    declare @Table varchar(100)
    declare @PK varchar(100)
    declare cur cursor for select name,object_name(parent_obj) from sysobjects where xtype='P' and object_name(parent_obj) in ('table1', 'table2', 'table3')
    open cur
    fetch next from cur into @PK, @Table
    while @@fetch_status = 0
    begin
    print ('alter table ' + @Table + ' drop constraint ' + @PK )
    fetch next from cur into @PK, @Table
    end
    close cur
    deallocate cur

    print 'alter table ' + @Table + ' drop constraint ' + @PK

  10. #10
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    does this query return anything? if not, there's your answer.

    select name,object_name(parent_obj) from sysobjects where xtype='P' and object_name(parent_obj) in ('table1', 'table2', 'table3')

Posting Permissions

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