I was tasked recently with removing the full text indexes in Adventureworks for a demo. The full text indexes were causing a few extra items to appear in a SQL Compare demo and weren’t needed. The individual that had set up the VM I was using wasn’t sure what to do, so they asked me.

I logged on to AdventureWorks and right clicked the Production.Document table. I knew that had full text indexes because I’d tested them before. However, what I got was this:

The Full Text index part was grayed out. Strange, since the database was attached, and with a query, I could see FTS indexes below.

I suspected that the FTS extensions weren’t installed. I decided to check by running setup. When it started, I clicked the top item to "add features", as shown here.

That brought up a list of instances. The default is the top radio button below, but I selected the second one, which let me select an existing instance.

Next, I saw the features, and sure enough, FTS wasn’t checked.

I checked it and then clicked next to continue the installation.

Once this was done, I could run SSMS and sure enough, I could delete the FTS indexes (shown below).

I actually had two instances on this VM, but this FTS feature isn’t in SSMS. It comes from the instance. After I deleted these three indexes, I connected to the second instance and tried to delete the FTS indexes, but things were grayed out, as shown in the first image above.

I had to re-run setup for the second instance and add the FTS components there as well to delete the indexes from that database. Once that was done, I could easily delete all the FTS indexes and complete this simple task.

Filed under: Blog Tagged: full text search, sql server, syndicated