TSQL Scripting a Full Text Catalog & Index

1 Flares Twitter 0 Facebook 0 StumbleUpon 0 Google+ 1 LinkedIn 0 1 Flares ×

One thing that really annoys me in sql server management studio is that when I try to script out a full text catalog, it doesn’t include the full text index within it. The scripting out of sql server objects is an amazing feature, and maybe this is a little oversight, but it is frustrating nonetheless.

Changing a full text generally requires dropping and recreating the whole thing, choosing this option in sql server generally results in the following type of code, regardless of the contents of the full text catalog:

This is great, but recreating this index will miss any of the individual index members you may have added previously.

One way to overcome this is to generate the adding of the index – the only way I’ve found to do this is as part of the adding process. For example:

  1. On your newly created full text catalog (without any contents), right click and go to properties
  2. Go to tables/views and add the required index, but DONT click OK
  3. Choose the “Script” button, and “Script Action to New Query Window”

This should output something like the following:

You can now use this after the drop and recreate script to regenerate your full text index.

The Essential App Marketing Kit
Subscribe To My Newsletter To Get an Entire Chapter From The Book for FREE
Never display this again
1 Flares Twitter 0 Facebook 0 StumbleUpon 0 Google+ 1 LinkedIn 0 1 Flares ×