April 14, 2011 at 3:36 pm
I have the following query that gives me the table, full-text catalog, filegroup on which a full-text index is defined and the unique index on which the full-text index is based.
I also want to get the column(s) on which the full-text index is defined, but the catalog view sys.fulltext_indexes does not return this information.
Anyone know how I can get that?
SELECT
t.name AS TableName,
c.name AS FTCatalogName ,
f.name AS FileGroupName,
i.name AS UniqueIdxName
FROM
sys.tables t
INNER JOIN
sys.fulltext_indexes fi
ON
t.[object_id] = fi.[object_id]
INNER JOIN
sys.fulltext_catalogs c
ON
fi.fulltext_catalog_id = c.fulltext_catalog_id
INNER JOIN
sys.filegroups f
ON
fi.data_space_id = f.data_space_id
INNER JOIN
sys.indexes i
ON
fi.unique_index_id = i.index_id
AND fi.[object_id] = i.[object_id];
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 14, 2011 at 4:23 pm
What is the problem with fulltext_index_columns ?
SELECT
t.name AS TableName,
c.name AS FTCatalogName ,
f.name AS FileGroupName,
i.name AS UniqueIdxName,
cl.name AS ColumnName
FROM
sys.tables t
INNER JOIN sys.fulltext_indexes fi
ON t.[object_id] = fi.[object_id]
INNER JOIN sys.fulltext_catalogs c
ON fi.fulltext_catalog_id = c.fulltext_catalog_id
INNER JOIN sys.filegroups f
ON fi.data_space_id = f.data_space_id
INNER JOIN sys.indexes i
ON fi.unique_index_id = i.index_id
AND fi.[object_id] = i.[object_id]
INNER JOIN sys.fulltext_index_columns ftic
ON t.[object_id] = ftic.[object_id]
INNER JOIN sys.columns cl
ON ftic.[object_id] = cl.[object_id]
AND ftic.column_id = cl.column_id
* Noel
April 15, 2011 at 7:44 am
Hmm, the problem with sys.fulltext_index_columns is that I didn't know about that catalog view... 😛
Now I do, thank you!
Here is the revised query:
SELECT
t.name AS TableName,
c.name AS FTCatalogName ,
f.name AS FileGroupName,
i.name AS UniqueIdxName,
cl.name AS ColumnName
FROM
sys.tables t
INNER JOIN
sys.fulltext_indexes fi
ON
t.[object_id] = fi.[object_id]
INNER JOIN
sys.fulltext_index_columns ic
ON
ic.[object_id] = t.[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.filegroups f
ON
fi.data_space_id = f.data_space_id
INNER JOIN
sys.indexes i
ON
fi.unique_index_id = i.index_id
AND fi.[object_id] = i.[object_id];
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply