Post reply

How to remove all keys (including indexes) in a database?

  • January 25, 2011 at 3:15 pm

    #388712

    What is the easiest approach?

    Many thanks for any input.

  • January 25, 2011 at 3:18 pm

    #1278218

    What exactly do you want to remove?

    Primary keys?

    Foreign keys?

    Unique Constraints?

    All indexes?

    And why?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 25, 2011 at 3:23 pm

    #1278221

    You are right. Remove all indexes, contraints, ...

  • January 25, 2011 at 6:26 pm

    #1278256

    I have written code that does just this, however, just like Gail I'd like to know the business case behind doing this. In addition, I am curious to know what you have done so far to sove this requirement. Are you stuck on something specific, what?

  • January 26, 2011 at 12:05 am

    #1278301

    sys.indexes will get you a list of all of the indexes in a table.

    sys.key_constraints will get you a list of all of the primary key and unique constraints, and what table that they are on.

    sys.foreign_keys will get you a list of all of the foreign keys, and what table that they are on.

    sys.default_constraints will get you a list of all of the default constraints, and what table that they are on.

    sys.check_constraints will get you a list of all of the check constraints, and what table that they are on.

    Until you answer Gail's question, I'll leave it up to you to figure out how to use this information. (But I bet that Lynn's routine will pretty much handle everything... you just gotta let us know why you want to do this.)

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply