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
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.
January 20th, 2009 at 8:18 am
[...] Posted by bradmarsh under Microsoft, SQL Server, Software This post has moved to: http://bradmarsh.net/index.php/2008/05/15/show-all-full-text-indexs-in-mssql/ [...]
April 7th, 2011 at 10:05 am
I like the valuable information you provide in your content articles. I’ll book mark your web page and check out all over again here frequently. I’m quite certain I’ll study lots of new things right here! Good luck for the next!
June 2nd, 2011 at 11:17 am
I really enjoyed your blog. That is nice when you find something that is not only informative but entertaining. Excellent.
January 6th, 2021 at 7:21 pm
Buy Proxies…
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…