How To Generate Index Creation Scripts (many indexes)

Question

How To Generate Index Creation Scripts (many indexes)

Ricky Valencia

SSCommitted

Points: 1907
More actions
June 24, 2015 at 10:58 am

#301969

Hello!
I would just like to if anyone can share a script they use to generate indexes in SQL 2005.
I have 2 databases on a separate instance. I want to script out all indexes from database1 then execute it on database2.
Please advise on how to accomplish this task efficiently.
Thank you very much.
Best Regards,

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

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

Grant Fritchey SSC Guru Points: 398696 More actions · Answer 1

Here's an example[/url] of a PowerShell script that will script out the indexes. It's written to only pull the indexes from a single table. You can pretty easily modify it to get the indexes for all tables. That's how I'd do it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Lowell SSC Guru Points: 323504 More actions · Answer 2

there are dozens of scripts in the scripts section here on SSC that do what you are asking;

take a look and see if one will do the job for you:

http://www.sqlservercentral.com/search/?q=script+indexes&t=s&sort=relevance

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ricky Valencia SSCommitted Points: 1907 More actions · Answer 3

Hi Mr. Grant,

Thank you for your suggestion.

I've read both your books SQL Server Execution Plans and SQL Server Performance Tuning and I've learned so much from it. I keep going back to your books constantly. Thank you for sharing your knowledge to the world Sir.

Have a Great Day!

Best Regards,

Ricky

Ricky Valencia SSCommitted Points: 1907 More actions · Answer 4

Hi Lowell,

Thank you for your reply.

The link that you sent is very helpful. Thank you for sharing it.

Have a Great Day!

Best Regards

Ricky

Grant Fritchey SSC Guru Points: 398696 More actions · Answer 5

Thank you very much for the kind words. Good luck with your indexes.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning