Benjamin Wright-Jones

Exploring data and distributed systems [I also cross-post to]

August, 2008

  • Benjamin Wright-Jones

    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)


  • Benjamin Wright-Jones

    Finally some guidance on SQL Server and Hyper-V


    Just noticed this KB which outlines the support policy for SQL Server on Hyper-V.  Bottom line is that SQL Server 2005 is not supported as is failover clustering for both SQL Server 2005 and SQL Server 2008. 

Page 1 of 1 (2 items)