Gathering status and detail information for SQL Server Full Text Catalogs

By:   |   Comments (1)   |   Related: > Full Text Search


I have several Full Text Search enabled databases and these databases contain several catalogs. Very often, I deploy these databases to many servers, so I need to know if these are deployed correctly and also find out as quickly as possible.  I need to know the population progress as well, but using management studio is too slow and also very hard to find out how much the catalogs have been populated.


You can use a T-SQL Script to pull all the major information at once. The query below has been tested and works on SQL 2000, SQL 2005 and SQL 2008.

Checking by using SQL Management Studio

Before I explain how you get the information via T-SQL, this is typical way to look at the Full-Text Search catalogs by using SQL Server Management Studio.


Right Click on a catalog and you will get the general property information.



After that, if you click the "Tables/Views", you will get the information shown below. But very often you will have to wait awhile for this screen to populate depending on how busy SQL Server is and how the configuration was setup. In my case, I usually wait 10 to 60 seconds before the information is all displayed. If you run Profiler while pulling the "Tables/Views" information, you will see why it takes so long to pull. It runs many redundant queries to pull this information and a lot of it I don't even need. I often only need to check that "Track Changes" is set to Automatic or not.


If I have one database with one catalog, that is not much work, but if you have several catalogs and several databases this can take a long time and be very frustrating.  Also this doesn't clearly show the progress either without doing some calculations.

Information to pull

I need to pull the following information.

  • All the Catalog names under the database
  • Table Name
  • If the Catalog is Enabled or Disabled
  • Change Tracking Option
  • Populate Status
  • Row Count
  • Full Text Search Count
  • Difference between total row count and Full Text Search Count
  • Percent Completed
  • Full Text Search Location/Path

Sample Result

This is how it looks. Since the results are wide, I broke this into two screen shots.



T-SQL Code

This is how I wrote the query to capture this information.  I am sure there are other ways to pull this information, but this is one easy way to pull the data. This has been tested for SQL 2000 to SQL 2008. I have used cursors and several system stored procedures and also directly queried the system tables. In order to minimize the impact, I changed the transaction isolation level to read uncommitted to do dirty reads and avoid any potential blocking issues..

set transaction isolation level read uncommitted
set nocount on
declare @tbl sysname
declare @cat sysname
create table #temp_ca( 
TABLE_OWNER varchar(100),
TABLE_NAME varchar(256),
create table #temp_status(
Catalog varchar(64),
TblName varchar(64), 
[IsEnabled] bit,
ChangeTracking varchar(24),
PopulateStatus varchar(64),
RowCnt int,
FTS_CT int,
Delta int,
PercentCompleted varchar(128), 
path nvarchar(260)
insert into #temp_ca
exec sp_help_fulltext_tables 
declare ca_cursor cursor for
open ca_cursor
fetch next from ca_cursor into @tbl, @cat
while @@fetch_STATUS = 0
insert into #temp_status
cast (@cat as varchar(40)) Catalog
, cast(object_name( as varchar(25)) TblName
, cast(OBJECTPROPERTY(,'TableHasActiveFulltextIndex') as bit) as [IsEnabled]
, case isnull(OBJECTPROPERTY(,'TableFullTextBackgroundUpdateIndexon'),0) 
+ ISNULL(OBJECTPROPERTY(,'TableFullTextChangeTrackingon'),0) 
when 0 then 'Do not track changes'
when 1 then 'Manual'
when 2 then 'Automatic'
end [ChangeTracking]
, case FULLTEXTCATALOGPROPERTY ( @cat , 'PopulateStatus' ) 
when 0 then 'Idle' 
when 1 then 'Full population in progress'
when 2 then 'Paused' 
when 3 then 'Throttled' 
when 4 then 'Recovering' 
when 5 then 'Shutdown' 
when 6 then 'Incremental population in progress' 
when 7 then 'Building index' 
when 8 then 'Disk is full. Paused.'
when 9 then 'Change tracking'
end PopulateStatus
, si.RowCnt, fulltextcatalogproperty(@cat, 'ItemCount') FTS_CT 
, si.RowCnt - fulltextcatalogproperty(@cat, 'ItemCount') Delta 
, cast ( 100.0 * fulltextcatalogproperty(@cat, 'ItemCount') 
/ cast(si.RowCnt as decimal (14,2))
as varchar) +'%' as PercentCompleted
, ISNULL(cat.path, 'Check Default Path')
dbo.sysobjects as tbl
INNER JOIN sysusers as stbl on stbl.uid = tbl.uid
INNER JOIN sysfulltextcatalogs as cat 
on (cat.ftcatid=OBJECTPROPERTY(, 'TableFullTextCatalogId')) 
AND (1=CasT(OBJECTPROPERTY(, 'TableFullTextCatalogId') as bit))
INNER JOIN sysindexes as si on = 
where si.indid in (0,1) and = object_id(@tbl)
fetch next from ca_cursor into @tbl, @cat
close ca_cursor
deallocate ca_cursor
select * from #temp_status
drop table #temp_ca
drop table #temp_status
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Kun Lee Kun Lee is a database administrator and his areas of interest are database administration, architecture, data modeling and development.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, September 22, 2016 - 9:48:26 AM - Morris Back To Top (43391)

 Hi Lee,

Great Effort. I need some help when I run this on SQL 2012 it's not returning any rows back. I ran when FULL Text index population was in progress. 

I understand you have tested it on SQL 2005 and 2008, but do you see any issue while running this on SQL2012 ENT version?

Thanks for your time


get free sql tips
agree to terms