How to drop large amount of index and constraint by sql?

  • Hi,

    We need to drop large amount of indexes and constraints and if we use SQL Analyzer to maually drop each of them this task would be painful. Is there any SQL scripts can easily perform this task?

    Thanks and regards,

    Wallace

  • Chan Wai Yin (11/6/2010)


    We need to drop large amount of indexes and constraints and if we use SQL Analyzer to maually drop each of them this task would be painful. Is there any SQL scripts can easily perform this task?

    Yes.

    Just write a query against system tables building the drop statements. Since only you know how to identify the targetted indexes/constraints only you can build such a query.

    Once the first query generates the needed stataments, check the script then execute.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • PaulB-TheOneAndOnly (11/7/2010)


    Chan Wai Yin (11/6/2010)


    We need to drop large amount of indexes and constraints and if we use SQL Analyzer to maually drop each of them this task would be painful. Is there any SQL scripts can easily perform this task?

    Yes.

    Just write a query against system tables building the drop statements. Since only you know how to identify the targetted indexes/constraints only you can build such a query.

    Once the first query generates the needed stataments, check the script then execute.

    What's the name of those system tables for index and constraints? Thanks a lot.

    Best regards,

    Wallace

  • sysindexes for the indexes (will need a join to sysobject or the object_name function to get the table name)

    sysobjects for the constraints (will need a join to sysobject or the object_name function to get the table name)

    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

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

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