Brad Marsh’s Weblog

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

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’

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.

Twitter This

Usuario: Password: