Delen via

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

  • Artikel

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.