Get all Fulltext catalog paths on a SQL Server

On some of our SQL Servers the Fulltext catalog locations are not excluded from anti-virus scans so I was after an easy way to get this information quickly. Once again Powershell proves its worth!

Just change the variable $server to query a particular server. The script will list all fulltext catalogs on the instance.

# Specify server name
$server = "sqlserver1"
# Load smo
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
# Create a server object with smo
$srv = New-Object Microsoft.SqlServer.Management.SMO.Server $server;
# Get databases from server
$databases = $srv.Databases;
# Iterate through each database
foreach($db in $databases)
	# Output the name and root path of each ft index
	$db.FullTextCatalogs | Select-Object -Property Name, RootPath;

The script will output something looking like below…

Name			RootPath
----			--------
FullText1		F:\FT_Catalogs\FullText1
FullText2		F:\FT_Catalogs\FullText2
FullText3		F:\FT_Catalogs\FullText3
FullText4		F:\FT_Catalogs\FullText4
FullText5		F:\FT_Catalogs\FullText5

Leave a Reply