Find objects using linked servers via powershell

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.


$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 AS ObjectName,
					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);
				$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
$outputs | SELECT ServerName, DatabaseName, LinkedServerName, ObjectName, ObjectType, Definition | Out-GridView

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.