Show all Full Text Index’s in MSSQL

I looked long and hard for something that would query all databases on a MS SQL server where it would display all of the full Text Indexes. However there is really not that much I could find. However I came across this query

sp_msforeachdb ‘use [?] exec sp_help_fulltext_catalogs’

 

we all know that the SP_msforeachdb has the ability to query all databases on the server, then we combine that with:

exec sp_help_fulltext_catalogs’

which shows us

image

As you can see it only shows you for the database selected nothing more.

if you run the below query by itself then you will are unable to see the results in any clear manner, especially if you need to export them to a CSV or alike (like I needed to do)

sp_msforeachdb ‘use [?] exec sp_help_fulltext_catalogs’

image

 

So I got one of our brilliant Developers Adrian Foyn, do something where we can use this data into a readable format.

So he create a temp table against the master database, then we save the results to the table, then query and drop  the table

 

create table mytemp
(
FT_ID varchar(500),
FT_NAME varchar(500),
FT_PATH varchar(500),
DB_Name varchar(500),
col5 varchar(500)
)
go
sp_msforeachdb ‘use [?];insert into master..mytemp exec sp_help_fulltext_catalogs; update master..mytemp set DB_Name = ”?” where DB_Name=”0”’
go
select    *
from    mytemp
go
drop table mytemp

 

this will also show the database name which corresponds to the FT index.

 

works brilliantly.

Thanks Adrian.

This entry was posted on Thursday, May 15th, 2008 at 6:42 am and is filed under Microsoft, SQL Server, Software . You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

4 Responses to “Show all Full Text Index’s in MSSQL”

Leave a Reply