SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL Server does not script indexes by default

Here is a got-ya that happened to me.  In SQL Server I want to generate a script of all the tables in case I need to re-create them.  This is usually the case in a development environment when I’m building a database.  So in Object Explorer I click on the Tables folder, open the tab Object Explorer Details, select all the tables (making sure not to select the System Tables folder), right mouse click and select “Script Table as -> CREATE To -> File”.

Great, I can now drop tables or modify them and can quickly get them back to the way they were because I did the right thing in creating a file with the scripts for them (maybe doing this daily).

So I drop a table, then go to my scripts, find the one for that table and execute it to recreate it.  Uh-oh, where have the indexes gone?  Well, by default, SQL Server does NOT script out the indexes, so they will not be re-created!  To change this, go to Tools -> Options -> SQL Server Object Explorer -> Scripting.  Set “Script indexes” to true.  Also note that “Script triggers” is set to false by default.

So, one of the first things you should do is change the option “Script indexes” to true so you can avoid trying to remember what the indexes were like I had to do.  It was a pain!

James Serra's Blog

James is a big data and data warehousing technology specialist at Microsoft. He is a thought leader in the use and application of Big Data technologies, including MPP solutions involving hybrid technologies of relational data, Hadoop, and private and public cloud. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 30 years of IT experience. James is a popular blogger (JamesSerra.com) and speaker, having presented at dozens of PASS events including the PASS Business Analytics conference and the PASS Summit. He is the author of the book “Reporting with Microsoft SQL Server 2012”. He received a Bachelor of Science degree in Computer Engineering from the University of Nevada-Las Vegas.


Posted by Anonymous on 29 July 2011

Pingback from  Dew Drop – July 29, 2011 | Alvin Ashcraft's Morning Dew

Posted by Jakub Dvorak on 29 July 2011

Good:) I was always bothering with Tasks -> Generate script whenever I wanted to script completely everything related to object.

Posted by David McKinney on 1 August 2011


Unless I'm mistaken Tasks -> Generate Script also generates based on the options whose defaults are set under Tools / Options (as James discussed).  The key is to review these options to your liking, and then subsequently generated scripts will follow.

But I agree that using Object Explorer Details to generate scripts is a god-send.

Posted by HLogic on 1 August 2011

The latest gotcha I have noted is in SSMS 2005 with SP4 applied (Version 9.00.5000.00), the TEXTIMAGE_ON clause is no longer scripted (even when Script file groups is True) and I don't see an option to include/exclude it.

Posted by cbedford on 1 August 2011

This may be a silly question but I can not select more than one table in SSMS 2008.  Is there a way around this?



Posted by HLogic on 1 August 2011

That can only be done on the Object Explorer Details tab.  <F7> to toggle it...

Posted by sharath.chalamgari on 1 August 2011

Need to use the detailed tab in object explorer

Posted by jaybosco on 1 August 2011

Hi Guys,

Try out this another option, Create the query without indexes, Doesnt matter how it is recreated, but in any state if you want to decide on the Index type then u can directly fix in the Clusterd or Non-clustered index, by just using the tuning Wizard, coz you cant anticipate the nature of the data, rather explicitly implying the Index.

Run a script without indexes and recreate Clusterd.. I think this would be a simple way...

Posted by pat.hall on 1 August 2011

DATA_COMPRESSION is also not scripted by default.

Posted by kobusv on 22 July 2013

Thanks James

Leave a Comment

Please register or log in to leave a comment.