Credits:
Description
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]; |