How to programmatically export an SQL schema using Microsoft.SqlServer.Management.Smo

Question

I created a simple C # utility that uses Microsoft.SqlServer.Management.Smo objects to export a database schema. This works fine until I added the full text index.

When it exports SQL to create a full text index, it does not include the columns that are defined in the index. For example, suppose I have a table called “Recipes” with 2 columns included in the FT index named “Recipe Name” “Description”. Resetting the circuit using my utility, you get the following SQL:

CREATE FULLTEXT INDEX ON [dbo].[Recipes] KEY INDEX [PK_Recipes]ON ([ft], FILEGROUP [PRIMARY]) WITH (CHANGE_TRACKING = AUTO, STOPLIST = SYSTEM) 

What I expect will be reset, this (note the columns):

 CREATE FULLTEXT INDEX ON [dbo].[Recipes]( [Description] LANGUAGE [English], [RecipeName] LANGUAGE [English] ) KEY INDEX [PK_Recipes]ON ([ft], FILEGROUP [PRIMARY]) WITH (CHANGE_TRACKING = AUTO, STOPLIST = SYSTEM) 

Here is C # that generates the schema from the database file:

 static void GenerateScript(string sourceDbPath, string destinationScriptPath) { try { string connString = string.Format(@"Data Source=.;AttachDbFilename={0};Integrated Security=True;User Instance=True", sourceDbPath); SqlConnection sqlConn = new SqlConnection(connString); ServerConnection serverConn = new ServerConnection(sqlConn); Server server = new Server(serverConn); Database database = server.Databases[sourceDbPath]; Transfer transfer = new Transfer(database); ScriptingOptions options = new ScriptingOptions(); options.AppendToFile = false; // Overwrite file options.ClusteredIndexes = true; options.Indexes = true; options.DriAll = true; options.Triggers = true; options.Bindings = true; options.Default = true; options.IncludeDatabaseContext = false; options.IncludeHeaders = true; options.FullTextIndexes = true; options.SchemaQualify = true; options.SchemaQualifyForeignKeysReferences = true; options.ScriptSchema = true; options.ScriptData = false; options.ScriptDrops = false; options.FileName = destinationScriptPath; transfer.Options = options; transfer.CopyAllFullTextCatalogs = true; transfer.CopyAllFullTextStopLists = true; transfer.CopyAllTables = true; transfer.ScriptTransfer(); } catch(Exception ex) { Console.WriteLine(ex.ToString()); Environment.Exit(-1); } } 

Can anyone notice what I am missing?

Answer 1

I could not find a combination of script parameters that would include the columns in the full text creation instructions.

So instead, I queried the database to get the full text index information by doing sp_help_fulltext_tables and sp_help_fulltext_columns stores the procs. This allowed me to write instructions manually.

Not perfect, but it works.