This post is part of a blog series which focuses on translating compatibility views in SQL Server to their respective dynamic management objects.  You can find a list of all of the deprecated views in the introduction post.

The compatibility view sysfulltextcatalogs is used to describe full text catalog.  For every catalog in the database there is one row returned from the compatibility view.

The catalog view sys.fulltext_catalogs replace sysfulltextcatalogs.  Like the compatibility view, there is one row returned for every full text catalog from sys.fulltext_catalogs.

Status Column

The compatibility view contains a single status column.  The status column contains only one know value; which is 0×1 to indicate if it is the default catalog.

Query Via sysfulltextcatalogs

With only the one value in the status column, queries against sysfulltextcatalogs are fairly simple.  Using the query, provided in Listing 1, a query against the compatibility view contains the other three columns in the view without any formatting.  The value in pathcolumn will be NULL if the path for the fulltext catalog is in the default location.

--Listing 1 – Query for sys.sysfulltextcatalogs
SELECT  ftcatid
      , name
      , path 
      , CONVERT(INT,status & 0x1) / 1 AS is_default
      , status
FROM sysfulltextcatalogs

Query via sys.fulltext_catalogs

In similar fashion, queries against sys.fulltext_catalogs are also fairly simple.  As the query in Listing 2 demonstrates, the status column is replace is the is_default column.  Beyond that, the other three columns from the compatibility view are fully represented with a single name change with the ftcatid column.  In addition to the columns from the original compatibility view there are some new properties exposed in the catalog view.  There are values to identify the accent sensitivity, file group, file, principal that owns the catalog, and import status for the fulltext catalog.

--Listing 2 – Query for sys.fulltext_catalogs
SELECT  fulltext_catalog_id AS ftcatid
      , name
      , path
      , is_default
      , is_accent_sensitivity_on
      , data_space_id
      , file_id
      , principal_id
      , is_importing
FROM sys.fulltext_catalogs

Summary

In this post, the use of sysfulltextcatalogs over sys.fulltext_catalogs was reviewed.  Making the change from the compatibility view to the catalog view is a fairly simple task with only a single column needing to be renamed. After reading all of this, do you see any reason to continue using sysfulltextcatalogs?  Is there anything missing from this post that people continuing to use the compatibility view should know?

Follow me on Twitter at StrateSQL.

Original article: Lost in Translation – Deprecated System Tables – sysfulltextcatalogs

©2012 Strate SQL. All Rights Reserved.

Related posts:

1. Lost in Translation – Deprecated System Tables – sysconfigures
2. Lost in Translation – Deprecated System Tables – sysfilegroups
3. Lost in Translation – Deprecated System Tables – syscurconfigs