Get Fulltext Index Composition

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

SQL Server 2014: Tested

SQL Server 2016: Tested

SQL Server 2017: Not Tested

Credits:

Author:ChillyDBA

Date: 6 Jun 2020

Description

Returns the composition of all FTIs in a database

Code

USE MyDatabaseSELECTtblOrVw.[name] AS TableOrViewName,tblOrVw.[type_desc] AS TypeDesc,tblOrVw.[stoplist_id] AS StopListID,c.name AS FTCatalogName ,cl.name AS ColumnName,i.name AS UniqueIdxNameFROM(SELECT TOP (1000)idxs.[object_id],idxs.[stoplist_id],tbls.[name],tbls.[type_desc]FROM sys.fulltext_indexes idxsINNER JOIN sys.tables tblson tbls.[object_id] = idxs.[object_id]union allSELECT TOP (1000)idxs.[object_id],idxs.[stoplist_id],tbls.[name],tbls.[type_desc]FROM sys.fulltext_indexes idxsINNER JOIN sys.views tbls -- 'tbls' reused here to mean 'views' on tbls.[object_id] = idxs.[object_id]) tblOrVwINNER JOIN sys.fulltext_indexes fion tblOrVw.[object_id] = fi.[object_id]INNER JOINsys.fulltext_index_columns icONic.[object_id] = tblOrVw.[object_id]INNER JOINsys.columns clONic.column_id = cl.column_idAND ic.[object_id] = cl.[object_id]INNER JOINsys.fulltext_catalogs cONfi.fulltext_catalog_id = c.fulltext_catalog_idINNER JOINsys.indexes iONfi.unique_index_id = i.index_idAND fi.[object_id] = i.[object_id];