How to check if Full-text Search is used?

on

CHECK FOR ALL DATABASES
  • Check if Full-text catalogs are created:
    SELECT *
    FROM sys.fulltext_catalogs
  • Check if Full-text indexes are created:
    SELECT
        SCHEMA_NAME(tbl.schema_id) as SchemaName,
        tbl.name AS TableName,
        FT_ctlg.name AS FullTextCatalogName,
        i.name AS UniqueIndexName,
        scols.name AS IndexedColumnName
    FROM
        sys.tables tbl
    INNER JOIN
        sys.fulltext_indexes FT_idx
    ON
        tbl.[object_id] = FT_idx.[object_id]
    INNER JOIN
        sys.fulltext_index_columns FT_idx_cols
    ON
        FT_idx_cols.[object_id] = tbl.[object_id]
    INNER JOIN
        sys.columns scols
    ON
            FT_idx_cols.column_id = scols.column_id
        AND FT_idx_cols.[object_id] = scols.[object_id]
    INNER JOIN
        sys.fulltext_catalogs FT_ctlg
    ON
        FT_idx.fulltext_catalog_id = FT_ctlg.fulltext_catalog_id
    INNER JOIN
        sys.indexes I
    ON
            FT_idx.unique_index_id = i.index_id
        AND FT_idx.[object_id] = i.[object_id];

If these queries output any results, it indicates that Full-text Search is set up.

SYSTEM STORED PROCEDURES


You can use two system stored procedures for the checks but be aware that they will be deprecated by Microsoft in the future.

sp_help_fulltext_tables

sp_help_fulltext_catalogs


REFERENCE

Comments

Post a Comment