Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

SQL Server - How to generate a script with all database indexes

Post Views 1,160 views
Reading time 6 minutes

Hey guys!
In this post I would like to show you how to generate a script to export all database indexes, very useful for migrations and configurations of new environments.

How to generate a script with all database indexes using T-SQL

One of the fastest and most practical ways is using the script below, which will generate a select with all the indexes that exist in the base.

Result:

How to generate a script with all database indexes using SSMS

One of the easiest ways to export all database indexes to a SQL script is using the SQL Server Management Studio (SSMS) itself.

To do this, open the “Object Explorer”, right-click on the database you want to export the indexes and select the option “Tasks” and then “Generate Scripts…”

In this opened screen, you can go ahead, and, optionally, check the “Do not show this page again” option, to skip this introductory screen the next time you open this wizard.

On the next screen, you can use the default option “Script entire database and all database objects”, to export all database objects, or click on the “Select specific database objects” option to select only the objects you want.

To just export the indexes, I chose the option “Select specific database objects” and checked all the tables.

On the screen below, click on the “Advanced” button to display some interesting options in the generated script.

Remember to activate the “Script Full-Text Indexes”, “Script Indexes” and “Script Data Compression options” for a successful export.

An option that can be very useful is the “Types of data to script”. The default option is “Schema only”, which only exports the definition of objects. In addition to this option, you can choose “Data only”, which exports only data (good for dumping just a few tables) and “Schema and data”, which backs up both object definition and data.

On this screen, you can choose the destination of the generated script, which can be a Jupyter notebook, an SQL script saved on disk, the desktop or a new tab in SSMS. I chose the last option.

The next screen will show a summary of the actions and settings you selected in the previous steps.

Ready! Script to create tables and indexes successfully generated.

And that's it, folks!
I hope you enjoyed this tip and until next time!