Full Text Index – Find Index Details

Recently I’ve had an issue with the primary key on a Full Text Index. This SQL query was helpful in highlighting the primary key which was being used:

SELECT tblOrVw.[name] AS TableOrViewName
	,tblOrVw.[type_desc] AS TypeDesc
	,tblOrVw.[stoplist_id] AS StopListID
	,c.name AS FTCatalogName
	,cl.name AS ColumnName
	,i.name AS UniqueIdxName
FROM (
	SELECT idxs.[object_id]
		,idxs.[stoplist_id]
		,tbls.[name]
		,tbls.[type_desc]
	FROM sys.fulltext_indexes idxs
	INNER JOIN sys.tables tbls ON tbls.[object_id] = idxs.[object_id]
	
	UNION ALL
	
	SELECT idxs.[object_id]
		,idxs.[stoplist_id]
		,[name]
		,[type_desc]
	FROM sys.fulltext_indexes idxs
	INNER JOIN sys.VIEWS vws ON vws.[object_id] = idxs.[object_id]
	) tblOrVw
INNER JOIN sys.fulltext_indexes fi ON tblOrVw.[object_id] = fi.[object_id]
INNER JOIN sys.fulltext_index_columns ic ON ic.[object_id] = tblOrVw.[object_id]
INNER JOIN sys.columns cl ON ic.column_id = cl.column_id
	AND ic.[object_id] = cl.[object_id]
INNER JOIN sys.fulltext_catalogs c ON fi.fulltext_catalog_id = c.fulltext_catalog_id
INNER JOIN sys.indexes i ON fi.unique_index_id = i.index_id
	AND fi.[object_id] = i.[object_id];

Code initially from here, but has been tweaked slightly.

Leave a Reply

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