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

Question

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

Chan Wai Yin

SSCommitted

Points: 1883
More actions
November 6, 2010 at 11:16 am

#242625

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

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

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

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

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.

Chan Wai Yin SSCommitted Points: 1883 More actions · Answer 2

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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