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

About the author

Bradley Schacht

Bradley Schacht is a Senior Program Manager on the Microsoft Azure Synapse Analytics team based in Jacksonville, FL. He has worked with Microsoft SQL Server and Azure data services since 2009 as a consultant, trainer, and architect. He has co-authored 4 SQL Server and Power BI books, most recently the Microsoft Power BI Quick Start Guide. Bradley enjoys solving interesting problems and teaching others to use new technology. He frequently presents at community events around the country, is a contributor to sites such as SQLServerCentral.com, and is a member of the Jacksonville SQL Server User Group (JSSUG).

Add comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Bradley Schacht

Bradley Schacht is a Senior Program Manager on the Microsoft Azure Synapse Analytics team based in Jacksonville, FL. He has worked with Microsoft SQL Server and Azure data services since 2009 as a consultant, trainer, and architect. He has co-authored 4 SQL Server and Power BI books, most recently the Microsoft Power BI Quick Start Guide. Bradley enjoys solving interesting problems and teaching others to use new technology. He frequently presents at community events around the country, is a contributor to sites such as SQLServerCentral.com, and is a member of the Jacksonville SQL Server User Group (JSSUG).

Follow Me