This script just executes a query on a per-database level to find objects via their definitions in the sys.all_sql_modules table that reference a linked server defined on the current server. Right now it just spits the results out to a gridview. Minimally tested, use at your own risk.
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$outputs = @();
$servers = gc -Path 'C:\Servers.txt'
$servers | %{
$srvName = $_
$srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
$srvConn.ServerInstance = $_
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn
$srv.LinkedServers | %{
$linkedServerName = $_.Name
$srv.Databases | %{
$linkedSQL = "
SELECT
o.name AS ObjectName,
o.type_desc,
LEFT(m.definition, 100) AS ObjectDefinition
FROM $($_.Name).sys.all_sql_modules m
INNER JOIN $($_.Name).sys.objects o ON m.object_id = o.object_id
WHERE m.object_id > 0
AND m.definition LIKE '%$linkedServerName%'
"
$dr = $srvConn.ExecuteReader($linkedSQL);
while($dr.Read()){
$output = New-Object -TypeName PSObject -Property @{
ServerName = $srvName
DatabaseName = $_.Name
LinkedServerName = $linkedServerName
ObjectName = $dr.GetString(0)
ObjectType = $dr.GetString(1)
Definition = $dr.GetString(2)
}
$outputs += $output
}
$dr.Close();
}
}
}
$outputs | SELECT ServerName, DatabaseName, LinkedServerName, ObjectName, ObjectType, Definition | Out-GridView
Sql Server Powershell 