Creating a full-text catalog in SQL Server using SQL

You can create a full-text catalog in SQL Server using SQL commands in the SQL Query Editor.

The steps below grant the REFERENCES permission to a user or role for a specific full-text catalog. If you later add another full-text catalog to the database that will be used by a different XML column, for example, you must grant the REFERENCES permission again for the new full-text catalog. If you prefer to grant this privilege in a different way, skip the appropriate steps below and send the appropriate GRANT statement to the database instead.

Steps:

1. In the SQL Query Editor window, type and execute the following query to check whether the database has been enabled for full-text searching. For example, if the database is named mygdb:

   SELECT DATABASEPROPERTY('mygdb', 'IsFulltextEnabled');

   If the value 1 is returned, the database is already full-text enabled—skip to step 7. If another value is returned, the database must be enabled for full-text searching using Management Studio before you can continue.

2. In SQL Server Management Studio's Object Explorer, click the plus sign (+) next to the appropriate SQL Server, then click the plus sign next to Databases.
3. Right-click the database in which the XML column will be created and click Properties.
4. Click Files under Select a Page on the left in the Database Properties dialog box.
5. Check Use full-text indexing in the page on the right if it is not already checked.
6. Click OK in the Database Properties dialog box.
7. In the SQL Query Editor window, select the database in which the XML column will be created using the drop-down list on the SQL Editor toolbar or by executing the USE command. For example:

   USE sde;

8. Create a full-text catalog in the database using the CREATE FULLTEXT CATALOG statement.

   For example, if you're using SQL Server 2005:

   CREATE FULLTEXT CATALOG SDE_DEFAULT_CAT IN PATH 'C:\Data\Fulltext' AS DEFAULT;

   If you are using SQL Server 2008 or later release:

   CREATE FULLTEXT CATALOG SDE_DEFAULT_CAT AS DEFAULT;

   If you provide a name other than SDE_DEFAULT_CAT, you must modify the value of the XML_IDX_FULLTEXT_CAT parameter for the DEFAULTS keyword in the SDE_dbtune table. You can optionally set other properties of the full-text catalog such as providing a location on disk where the files will be stored and setting the catalog as the default full-text catalog for the database.

9. Grant REFERENCES permission on this full-text catalog to the user who will own the XML column, or grant this permission to the role to which the user belongs.

   For example:

   GRANT REFERENCES ON FULLTEXT CATALOG :: SDE_DEFAULT_CAT TO dataOwners;