Benjamin Wright-Jones

Exploring Information, Infrastructure and Solution Architecture

August, 2008

Posts
  • Benjamin Wright-Jones

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

    • 5 Comments

    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);
                }

            }
        }
    }

  • Benjamin Wright-Jones

    Finally some guidance on SQL Server and Hyper-V

    • 1 Comments

    Just noticed this KB http://support.microsoft.com/kb/956893 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)