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!

About James Serra

James currently works for Microsoft specializing in big data and data warehousing using the Analytics Platform System (APS), a Massively Parallel Processing (MPP) architecture. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence/MDM architect and developer, specializing in the Microsoft BI stack. He is a SQL Server MVP with over 25 years of IT experience.
This entry was posted in SQL Server, SQLServerPedia Syndication. Bookmark the permalink.

7 Responses to SQL Server does not script indexes by default

  1. Garry Bargsley says:

    What about the Tasks / Generate Scripts option. That gives you a lot more flexibility with advanced options that you can have the tool do for you when generating the output for the objects you select.

  2. Kiran Ramaswamy says:

    That section is not visible under my list of options. Any idea why not? Is this something that has to be set on the machine which is running the server? I’m running SSMS from a client machine, and not the machine that actually has the server installed on it.

  3. Don Bricker says:

    Thank You. A useful piece of information.

  4. Shrikant says:

    Thank You. It worked for me…

  5. Michele says:

    Is there a way to permanently change the default for ‘indexes’ to True? It is really stupid to make it False by default and updating the same values over and over is getting really tiresome. I would also permanently script triggers as well.

    • Steve says:

      Michele, this appears to be settings located in SQL Studio Management Studio and not on the database itself. So, you’ll have to set this for every SSMS install that you need this functionality to exist on. If there’s a way to script the SSMS install to set these options, I’m unaware of a way to do so.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>