SQL Server Full Text Search – The ObjectPropertyEx Function

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.

About these ads

2 Responses to “SQL Server Full Text Search – The ObjectPropertyEx Function”

  1. SQL Saturday Orlando Full Text Searching Session « Arcane Code Says:

    [...] me now? Checking to see if FTS is installed. Exploring SQL Servers FullTextCatalogProperty Function Using the ObjectPropertyEx Function Using FORMSOF in SQL Server Full Text Searching Creating Custom Thesaurus Entries in SQL Server [...]

  2. SQL Server Full Text Searching at the Atlanta Code Camp « Arcane Code Says:

    [...] me now? Checking to see if FTS is installed. Exploring SQL Servers FullTextCatalogProperty Function Using the ObjectPropertyEx Function Using FORMSOF in SQL Server Full Text Searching Creating Custom Thesaurus Entries in SQL Server [...]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 101 other followers

%d bloggers like this: