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