Automations

Find All Full Text Indexes

Want to find all the Full Text Indexes across every database? You could do one at a time, but that’s annoying, and there’s always a better way of doing it.

So… to get them all try this little piece of sql logic:

use master;
set nocount on
declare @get_all_full_text_catalogs varchar(max)
set @get_all_full_text_catalogs = ''
select @get_all_full_text_catalogs = @get_all_full_text_catalogs + '
use [' + name + '];' + char(10) +
'
select
''DatabaseName'' = db_name()
, ''TableName'' = st.name
, ''FTCatalogName'' = sfc.name
, ''FileGroupName'' = sf.name
, ''IndexName'' = si.name
, ''ColumnName'' = sc.name
from
sys.tables st
join sys.fulltext_indexes sfti on st.[object_id] = sfti.[object_id]
join sys.fulltext_index_columns sftic on sftic.[object_id] = st.[object_id]
join sys.columns sc on sftic.column_id = sc.column_id and sftic.[object_id] = sc.[object_id]
join sys.fulltext_catalogs sfc on sfti.fulltext_catalog_id = sfc.fulltext_catalog_id
join sys.filegroups sf on sfti.data_space_id = sf.data_space_id
join sys.indexes si on sfti.unique_index_id = si.index_id and sfti.[object_id] = si.[object_id]; ' + char(10)
from sys.databases where name not in ('master', 'model', 'msdb', 'tempdb') order by name asc

exec (@get_all_full_text_catalogs) --for xml path(''), type

Hope this is useful.

Advertisements

1 reply »

  1. Here’s the same thing, but for 2005… If thats ever needed.

    use [MyDatabase];

    select
    ‘DatabaseName’ = db_name()
    , ‘TableName’ = st.name
    , ‘FTCatalogName’ = sfc.name
    –, ‘FileGroupName’ = sf.name
    , ‘IndexName’ = si.name
    , ‘ColumnName’ = sc.name
    from
    sys.tables st
    join sys.fulltext_indexes sfti on st.[object_id] = sfti.[object_id]
    join sys.fulltext_index_columns sftic on sftic.[object_id] = st.[object_id]
    join sys.columns sc on sftic.column_id = sc.column_id and sftic.[object_id] = sc.[object_id]
    join sys.fulltext_catalogs sfc on sfti.fulltext_catalog_id = sfc.fulltext_catalog_id
    –join sys.filegroups sf on sfti.data_space_id = sf.data_space_id
    join sys.indexes si on sfti.unique_index_id = si.index_id and sfti.[object_id] = si.[object_id];

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s