Script Indexes With Your Tables

Scripting tables inside of SQL Server Management Studio is a very simple task.  Just right click and select script table.  The problem is that, but default, the indexes on a table are not part of the script.  Rather than scripting the indexes separately from the table you can tell management studio that you want the indexes included.  Click on Tools then Options and expand the SQL Server Object Explorer section on the left side.  From there select Scripting and scroll down to the section for “Table and view options”.  There are numerous options under this section for scripting tables.  The one we are interested in this case is the “Script indexes” setting; this is False by default.  Just change it to true and now whenever you right click and script a table it will include all index, clustered and non-clustered, in the create table script.

Some other usefull settings (some of which are enabled by default that you may want to turn off at some point) are:
*Include IDENTITY property
*Schema qualify foreign key references
*Script CHECK constraints
*Script defaults
*Script foreign keys
Script full-text indexes
*Script primary keys
Script statistics
Script triggers
NOTE: A * before the item denotes that it is enabled by default

Bradley Schacht

Bradley Schacht is a Data Platform Solution Architect on the state and local government team with Microsoft based in Jacksonville, FL. He has co-authored 3 SQL Server books including "SQL Server 2014 Professional Administration". As a former consultant and trainer, he uses his experience on many parts of the Microsoft BI and data platform to help customers deliver the best possible solutions. Bradley frequently presents at community events around the country. He is a contributor to sites such as SQLServerCentral.com and an active member of the Jacksonville SQL Server User Group (JSSUG).

You may also like...

Leave a Reply