List all full-text search catalogues on an instance

With the following script you can easily find all FTS catalogues and their state on the SQL Server instance.

This could come handy when you are migrating a database as you should take care of rebuilding all FTS catalogues and for that – you will need a list of what should be rebuilt.

select sd.name as database_name, fts.name as fts_name, fts.catalog_id,
fts.is_importing,fts.is_paused,fts.status_description
from sys.dm_fts_active_catalogs fts
inner join sys.databases sd
on fts.database_id = sd.database_id

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.