Cool Tips‎ > ‎Indexes‎ > ‎

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 MyDatabase 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 TOP (1000) 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 TOP (1000) idxs.[object_id], idxs.[stoplist_id], tbls.[name], tbls.[type_desc] FROM sys.fulltext_indexes idxs INNER JOIN sys.views tbls -- 'tbls' reused here to mean 'views' on tbls.[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];




Comments