Find FullText Search settings information for all catalogs & databases (in a jiffy)

Posted on by

Good morning folks – Ever worked on full text search in SQL Server and found it frustrating due to lack of information when anything goes bad & you are left poking around on what information to collect. Here is a script that you can use to collect Full-text (FTS) information for catalogs in ALL your databases. This will tell you the following,

  • Catalog Name & catalog ID
  • Item count
  • Population Status
  • Is Change Tracking enabled or not
  • Last Crawl – was it FULL, Incremental or UPDATE. Also gives you time of last crawl
  • Path of catalog storage location
EXEC sp_msforeachdb 'IF EXISTS (select * from ?.sys.fulltext_catalogs) BEGIN PRINT ''In Database: ?'' SELECT cat.name AS [CatalogName], cat.fulltext_catalog_id AS [CatalogID], FULLTEXTCATALOGPROPERTY(cat.name,''LogSize'') AS [ErrorLogSize], FULLTEXTCATALOGPROPERTY(cat.name,''IndexSize'') AS [FullTextIndexSize], FULLTEXTCATALOGPROPERTY(cat.name,''ItemCount'') AS [ItemCount], FULLTEXTCATALOGPROPERTY(cat.name,''UniqueKeyCount'') AS [UniqueKeyCount], FULLTEXTCATALOGPROPERTY(cat.name,''PopulateStatus'') AS [PopulationStatus], tbl.change_tracking_state_desc AS [ChangeTracking], tbl.crawl_type_desc AS [LastCrawlType], tbl.crawl_start_date AS [LastCrawlSTARTDate], tbl.crawl_end_date AS [LastCrawlENDDate], ISNULL(cat.path,N'''') AS [RootPath], CAST((select(casewhen exists(select distinct object_id from sys.fulltext_indexes fti where cat.fulltext_catalog_id = fti.fulltext_catalog_id and OBJECTPROPERTY(object_id, ''IsTable'')=1)then 1 else 0 end)) AS bit) AS [HasFullTextIndexedTables] FROM ?.sys.fulltext_catalogs AS catLEFT OUTER JOIN ?.sys.filegroups AS fg ON cat.data_space_id = fg.data_space_id LEFT OUTER JOIN ?.sys.database_principals AS dp ON cat.principal_id=dp.principal_idLEFT OUTER JOIN ?.sys.fulltext_indexes AS tbl ON cat.fulltext_catalog_id = tbl.fulltext_catalog_id PRINT '''' END'
Advertisement