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.

[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
Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: