It can be quite useful to have a query that will list all of the tables in your database that currently hold full text indexes. Fortunately there is a function we can use, ObjectPropertyEx. We’ll combine it with a system view named sys.tables which gives us a list of all the tables in the current database.
Note, for todays example I will continue to use the AdventureWorks database, AdvWorksCatalog, and index I created in yesterday’s post on the FullTextCatalogProperty function.
ObjectPropertyEx takes two parameters. The first is the object ID of the table we want to work with. That’s why using sys.tables view is so nice, it already containts both the name of the table and it’s object ID. The second parameter is the name of the property we want to get, in this case TableHasActiveFulltextIndex will tell us if the table has an FTS (Full Text Search) index or not. Here’s a query that will list all of the tables with a full text index:
select t.[Name] as TableName
, ObjectPropertyEx(t.[object_id], ‘TableHasActiveFulltextIndex’) as IsFullTextIndexed
from sys.tables t
where ObjectPropertyEx(t.[object_id], ‘TableHasActiveFulltextIndex’) = 1
order by t.[Name]
TableName IsFullTextIndexed
——————– ——————
ProductDescription 1
This returns a list of the table name, and the value of 1 indicating the table does have a full text index on it. To get a list of all tables, simply omit the where clause. Tables without an full text index will have a 0 for the IsFullTextIndexed value.
We can further extend this function by using a second property, TableFulltextItemCount. This will tell us how many rows exist in the full text index for this table.
select t.[Name] as TableName
, ObjectPropertyEx(t.[object_id], ‘TableFulltextItemCount’) as NumberOfRows
from sys.tables t
where ObjectPropertyEx(t.[object_id], ‘TableHasActiveFulltextIndex’) = 1
order by t.[Name]
TableName NumberOfRows
——————- ————-
ProductDescription 762
In this case the number of rows matches the number of rows in the table.
This is because I have change tracking set to auto, and have a speedy system. Be aware there are times when this number won’t match the row count. This would primarily be when you have change tracking set to manual, have inserted new rows in to the source and have not issued the command to force the update of the full text index.
By comparing the TableFulltextItemCount to the number of actual rows in the source table, you can monitor your system health and activity. If you see the numbers grow ouside the normal boundaries, it can be an indication something is wrong. You can also use it for adjusting the frequency of manual update commands. If the numbers grow apart too much for your comfort, you may wish to make the updates occur more frequently. Conversely, if most of the time the numbers match you may wish to reduce the frequency of manual updates.
It’s not absolutely required to go to the sys.tables in order to get the count. If you already have the name of the table, you can use it in combination with the object_id function to get the count for that one table.
select ‘Production.ProductDescription’ as TableName
, ObjectPropertyEx(object_id(‘Production.ProductDescription’)
, ‘TableFulltextItemCount’) as NumberOfRows
TableName NumberOfRows
——————- ————-
ProductDescription 762
Using the above logic you could easily create a function to pass in the name of a single table and get the number of rows in the full text index.
As you can see, using ObjectPropertyEx with its two simple full text search related properties will give you some useful functionality for monitoring your system.
Arcane Code 
2 thoughts on “SQL Server Full Text Search – The ObjectPropertyEx Function”