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