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)


Comments (5)
  1. Stuntbeaver says:

    The libraries you need to reference are a little different from the namespaces (at least they were for me):



    Also 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.

  2. Stuntbeaver says:

    Forgot to say thanks for posting the example – I found the code quite handy.

  3. moff says:

    You can do this in one line with the new Powershell provider in SQL2008 (even if running against 2005) šŸ™‚

    sl SQLSERVER:SQL<server><instance>Databases<db>Tables;gci|%{gci ($.DisplayName + ‘Indexes’)|%{$.script()}} > c:indexes.txt

    Fair enough it’s not very readable, and it doesn’t include the exact scriptingoptions, but hey, it’s one line šŸ˜‰

    For reference:

    sl = set-location

    gci = get-childitem

    % = foreach-object

  4. superlatch says:

    Thanks for all the tips, that’s useful info.  

Comments are closed.

Skip to main content