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!
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.
Good point Garry, that is easier to use, but when using that option you also have to make sure to set Script Indexes to True.
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.
Thank You. A useful piece of information.
Thank You. It worked for me…
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.
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.
Thanks for that… you really saved me from doing a long workaround!
Pingback:Just notes… » Blog Archive » SSMS erzeugt für Tabellenskript keine Indizes
Thank you for sharing this information.
Exactly what I was looking for. Thanks!
thank you