Robert J. Hatton

It’s not uncommon to have a commonly used table copied into a number of different databases, potentially on different servers. We had a requirement recently to change the schema of a commonly used table, but had no idea of where it’s used. Rather than manually inspect every single SQL Server in the organization, I whipped up a PowerShell script to ask each database on each server if it has a copy of the desired table.

It works great, but it’s a bit crude. I didn’t include much in the way of error handling, but given how rarely this will be used, it’s probably not worth doing. It does give you the location of every copy of the table that you specify at the beginning of the script! Don’t forget to update $serverList with the names of your servers.

Clear-Host
Write-Host “Searching SQL Servers….”

#Set up the variables
$tblName = “Put table you want here”
$serverList = ‘server1′,’server2\instance’,’server3′,’server4′
$SQLServer = “”
$SQLDBName = “”
$uid =””
$pwd = “”
$db = “”
$tbl = “”
$connectionString = “”

foreach($SQLServer in $serverList)
{
#create a connection to the database
$connectionString = “Server=$SQLServer;Database = Master;Integrated Security=SSPI;”

#open the connection
$dbConn = New-Object System.Data.SqlClient.SqlConnection
$dbConn.ConnectionString = $connectionString
$dbConn.Open()

Write-Host “Server: ” $SQLServer

#Compose a SQL statement to find every database
$qry = @”
select d.[name] as dbName
from sys.databases d
where d.[name] not in (‘master’,’tempdb’,’model’,’msdb’)
“@

#Add the query to the connection
$dbCmd = $dbConn.CreateCommand()
$dbCmd.CommandText = $qry

#Execute the query
$dbList = $dbCmd.ExecuteReader()

#Format and display the results
$dbs = new-object “System.Data.DataTable”
$dbs.Load($dbList)

#Check each database for desired table
foreach($db in $dbs)
{
$dbName = $db[0]
#Write-Host “Checking database: ” $dbName

#open the connection
$tblConn = New-Object System.Data.SqlClient.SqlConnection
$tblConn.ConnectionString = $connectionString
$tblConn.Open()

#Compose a SQL statement to find every table
$qry = @”
select s.[name] as schemaName
, tbl.[name] as TableName
from $dbName.sys.tables tbl
left outer join $dbName.sys.schemas s
on s.[schema_id] = tbl.[schema_id]
“@

#Add the query to the connection
$tblCmd = $tblConn.CreateCommand()
$tblCmd.CommandText = $qry
#Write-Host “Query: ” $tblCmd.CommandText “`n”

#Execute the query
try {
$tblList = $tblCmd.ExecuteReader()
}
catch
{
Write-Host ” -Error accessing database” $dbName
}
$tbls = new-object “System.Data.DataTable”
$tbls.Load($tblList)
#Write-Host ($tbls | Format-Table | Out-String)
foreach($tbl in $tbls)
{
if($tbl[1] -eq $tblName)
{
Write-Host “Found table in ” $dbName, $tbl[0], $tbl[1]
}
}

}

}