SQLDMO - Listing all Table Indexes
 
Let's say you want to find the list of all indexes of a table. Although it is possible to use the SQL Server system tables to get this information, another solution is to use SQL-DMO to get this information. 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 get the list of indexes of a table.

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.
  • Get a handle to the list of indexes and then loop through each index.

Here is an example:


Module GetAllIndexes
    Sub Main()
        Dim oServer As SQLDMO.SQLServer     ' Points to a server
        Dim oDatabase As SQLDMO.Database    ' Reference to a database
        Dim oTable As SQLDMO.Table          ' Reference to a single table
        Dim oIndex As SQLDMO.Index          ' Reference to a single index
        Dim oList As SQLDMO.SQLObjectList   ' Hold result of the index
        Dim oColumn As SQLDMO.Column        ' Identifies one column of an index
        Dim strString As String             ' Used to store the ouput

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

            ' Make a reference to the "pubs" database
            oDatabase = oServer.Databases.Item("pubs")

            ' Make a reference to the "authors" table
            oTable = oDatabase.Tables.Item("authors")

            ' Iterate through each index of the table
            For Each oIndex In oTable.Indexes
                strString = oIndex.Name & " ("

                ' Get the list of columns participating in the index
                oList = oIndex.ListIndexedColumns()
                For Each oColumn In oList
                    strString = strString & oColumn.Name & ", "
                Next
                strString = strString.Substring(0, strString.Length - 2)
                strString = strString & ")"
                Console.WriteLine("{0}", strString)
            Next

            ' Release all references
            oServer.Application.Quit()

            ' Wait for user input
            Console.ReadLine()
        Catch ex As Exception
            Console.WriteLine("SQLDMO Error: {0}", ex.Message)
        End Try
    End Sub
End Module

The above program will print all the indexes of the authors table. In this output, apart from the normal indexes, you might also see indexes begining with the name _WA_SYS. These are not actual indexes, but statistical indexes created on the columns.

Home