Need a script to drop and create all indexes of a given database

Question

Need a script to drop and create all indexes of a given database

rene.moosens

SSCrazy

Points: 2214
More actions
July 8, 2010 at 4:21 am

#223627

Hi,
in Management Studio you can do for a single index or key the following SCRIPT INDEX AS... DROP and CREATE TO a new query editor window or file.
Is there a means to create a DROP and CREATE TO script for all the indexes and/or keys in a table.
I have to change collation in a database on more than 10000 columns and a lot of them are used in keys or indexes.
Can anyone help?
Best regards,

Viewing 8 posts - 1 through 7 (of 7 total)

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

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 1

my memory is not as good as it used to be, but i don't think you can do it using dynamic SQL. i think i tried this one time a year or so ago on one table and it wouldn't allow me. i had to script out each index's drop and create statements.

rene.moosens SSCrazy Points: 2214 More actions · Answer 2

I fond a very good article on the web:

SQL SERVER – Change Collation of Database Column – T-SQL Script – Consolidating Collations – Extention Script[/url]

from Brian Cidern

After adapting the scripts and follwing the instructions, it did a good job for me

One remark, you have to run sp_configure to enable 'Ole Automation Procedures' during generation of the CREATE script.

Jim McLeod SSCarpal Tunnel Points: 4057 More actions · Answer 3

alen teplitsky (7/8/2010)
my memory is not as good as it used to be, but i don't think you can do it using dynamic SQL. i think i tried this one time a year or so ago on one table and it wouldn't allow me. i had to script out each index's drop and create statements.

I have a similar memory, but it might be because it's not worth creating a script to run the commands one-time when you can just get the commands scripted.

Another option you could try would be to disable the indexes and then rebuild. It's a lot easier to script "ALTER INDEX <indexname> ON <schema>.<table> DISABLE" and then "ALTER INDEX <indexname> ON <schema>.<table> REBUILD", instead of worrying about the actual definition of the index (included columns, etc)

SumOfDavid Ten Centuries Points: 1357 More actions · Answer 4

Jim, excellent thought. I had created a function to script out the create and drop indexes for a table, and I should have just used that idea instead...would make it also easier to script cross database alter index calls.

David

sabuthomascochin Valued Member Points: 73 More actions · Answer 5

Try to use a db comparison tool like SQL Delta to compare and take script

i too had same problem

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Check out this thread:

http://www.sqlservercentral.com/Forums/Topic796512-391-1.aspx

There is a script there as well as a link to another thread that has multiple revisions for a script that will do what you are looking to accomplish.

A slight modification could likely also generate the drop statements for you.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

S. Kusen SSChampion Points: 10936 More actions · Answer 7

I don't necessarily have a script to provide, but depending on how many tables there are, you could, in SSSM, highlight the Indexes node under a table, pull up the Object Explorer Details, and then right click on the multiple-selected indexes and script them as drop and create to new window/file/agent job.

May not necessarily help in this situation, but it's something else to add to the discussion.

Regards,

Steve