How can I generate a T-SQL script for just the indexes?

I was trying to work out the best way to generate a T-SQL script for only the indexes and I thought that this would be really easy in the Management Studio interface.  I was mistaken.  SQL Server Management Studio also generates scripts for the tables when you want the indexes.  This is not great so I looked at other methods.  Method 1) Use T-SQL to generate a script using the metadata or method 2) use SMO (SQL Server Management Objects).  Although I like doing things with T-SQL, I thought I’d give SMO a try and below is the result.  I just hope this is made easier in future releases.

using System;
using System.Collections.Generic;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System.Text;
using System.Collections.Specialized;

namespace SQLRMOSMO
{
    class SMOTest   // Scripts all indexes for a specified database
    {
        static void Main(string[] args)
        {
            string servername;
            string databasename;
            servername = "<server\\instance>";
            databasename = "<database>";

            Server server = new Server(servername);
            Database db = server.Databases[databasename];
            ScriptingOptions so = new ScriptingOptions();
            so.ScriptDrops = false;
            so.Indexes = true;
            so.IncludeIfNotExists = false;
            so.DriForeignKeys = false;
            so.FileName = "c:\indexes.sql";
            so.AppendToFile = true;
            foreach (Table t in db.Tables)
            {
                foreach (Index i in t.Indexes)
                    i.Script(so);
            }

        }
    }
}

Comments

• Anonymous
  August 27, 2008
  PingBack from http://informationsfunnywallpaper.cn/?p=2594
• Anonymous
  August 28, 2008
  The libraries you need to reference are a little different from the namespaces (at least they were for me):Microsoft.SqlServer.ConnectionInfoMicrosoft.SqlServer.SMOAlso I noticed that "so.AppendToFile = false" should be set to true.Without this, it will only store script for the very last index (typically a primary key) in the text file. You will of course then need to make sure you truncate the text file at the start of the program.
• Anonymous
  August 28, 2008
  Forgot to say thanks for posting the example - I found the code quite handy.
• Anonymous
  September 04, 2008
  The comment has been removed
• Anonymous
  September 21, 2008
  Thanks for all the tips, that's useful info.