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: 397721 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Lowell SSC Guru Points: 323482 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://qa.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: 397721 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software