SQLDMO - Scripting All Indexes

Let's say you want to script all the indexes in a database. Although it is possible to directly use SQL Server for this, another solution is to use SQL-DMO to do this operation. SQL-DMO provides an object view of all SQL Server objects can be used in any COM-enabled environment. In this article, we will see how to use VB.NET and SQL-DMO to script all the indexes in a database for all tables.

The steps in getting the list of indexes using SQL-DMO are:

Connect to a SQL Server.
Connect to a database and to a table within the databse.
Iterate through all the tables in the database and for each table, iterate through the indexes of that table and script them.

Here is an example:


Module ScriptAllIndexes
    Sub Main()
        Dim oServer As SQLDMO.SQLServer
        Dim oDatabase As SQLDMO.Database
        Dim oTable As SQLDMO.Table
        Dim oIndex As SQLDMO.Index

        oServer = New SQLDMO.SQLServer
        Try
            ' Connect to the SQL Server
            oServer.Connect("lp-srinivas", "sa", "password")

            ' Connect to the "pubs" database
            oDatabase = oServer.Databases.Item("pubs")

            For Each oTable In oDatabase.Tables
                If (Left(oTable.Name, 3) <> "sys") Then
                    Console.WriteLine("Scripting for {0}", oTable.Name)
                    For Each oIndex In oTable.Indexes
                        Console.WriteLine(vbTab & "Scripting {0}", oIndex.Name)
                        oIndex.Script( _
                            SQLDMO_SCRIPT_TYPE.SQLDMOScript_Default Or _
                                SQLDMO_SCRIPT_TYPE.SQLDMOScript_AppendToFile Or _
                                SQLDMO_SCRIPT_TYPE.SQLDMOScript_Drops Or _
                                SQLDMO_SCRIPT_TYPE.SQLDMOScript_IncludeHeaders, _
                            "C:\Temp\AllIndexes.sql", _
                            SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_Default)
                    Next
                End If
            Next

            ' Cleanup all resources
            oServer.Application.Quit()
        Catch ex As Exception
            Console.WriteLine("SQLDMO Error: " & ex.Message)
        End Try
        Console.ReadLine()
    End Sub
End Module

The above program will script all the indexes in the pubs database and write the output to the AllIndexes.sql file. Note how we specify the scripting options using the VB.NET logical Or operator.

Home