Conditionally create a fulltext catalog in one line of SQL (from an installer)

full-text-search, installation, sql-server-2005

Solution

sys.fulltext_catalogs

IF EXISTS (SELECT 1 FROM sys.fulltext_catalogs
           WHERE name = 'foobar') ....

Problem

I'm building an installer using WiX (not keyworded since WiX is not the problem here) that shall create a fulltext index for a SQL Server Express 2005 Advanced Services database. Up to now I use the SQL ``` CREATE FULLTEXT CATALOG Foobar in path 'c:\Whereever' ``` Now, the installer will not remove the database on uninstall as it may contain valuable user data. Now when I update the software it will stumble over the creation of the already existing fulltext index. (Abort in installer, as it can't run that line of SQL) What I want to do is run the line conditionally if the fulltext index does not already exist. I'd expect something like ``` IF NOT EXISTS(SELECT * from sys.??????? WHERE name = 'Foobar') CREATE FULLTEXT CATALOG Foobar in path 'c:\Whereever' ``` (I just saw something similar to what I need here on stackoverflow) But where (and how) can I find out whether the index is recorded in a sys table (and which table is it?)

Original source