SQL Server PowerShell : Search for SQL Server Objects Using PowerShell

I have posted previously 3 different ways of searching for SQL Server Objects (How to Search for Columns in SQL Server ).

Here’s a fourth one!

Check out how powerful and flexible PowerShell is when you need to look for a database object. In the script below, I only search databases, tables, columns, and indexes. But in reality, really, sky is the limit!

Script to Search SQL Server Objects

[ps collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

#Search Script using SMO and PowerShell
#Script below looks for an object that contains the
#searchString text
#Current scope: databases, tables, columns, indexes
#Author: Donabel Santos

#assume this is the search string
#this can also be passed in as an argument when you run this PowerShell script
$searchString = "test"

#load assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

#create server object
$sqlserver = "(local)"
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver

#create an empty array
#we will store our results here
$result = @()

#search databases
#note that this expression: @{Name="Type";Expression={[string]"Database"}}
#allows us to display/store the result as a table
$result = $srv.Databases | `
where {$_.Name -match $searchString } | `
select name, `
@{Name="Type";Expression={[string]"Database"}}, `

foreach ($db in $srv.Databases)
#search tables
$dbName = $db.Name
$result += $db.Tables | `
where {$_.Name -match $searchString } | `
select name, `
@{Name="Type";Expression={[string]"Table"}}, `
@{Name="FullName";Expression={[string]($dbName +"." + $_.Name )}}

#search columns and indexes within tables
foreach ($tbl in $db.Tables)
$tblName = $tbl.Name

#search tables
$result += $tbl.Columns| `
where {$_.Name -match $searchString } | `
select name, `
@{Name="Type";Expression={[string]"Column"}}, `
@{Name="FullName";Expression={[string]($dbName +"." + $tblName +"." + $_.Name )}}

#search indexes
$result += $tbl.Indexes| `
where {$_.Name -match $searchString } | `
select name, `
@{Name="Type";Expression={[string]"Index"}}, `
@{Name="FullName";Expression={[string]($dbName +"." + $tblName +"." + $_.Name )}}

#display results, order by type
$result | sort type | Format-Table -autosize


Here’s a sample result:

Name                       Type     FullName
----                       ----     --------
FieldTestField             Column   test.Table1.FieldTestField
test_Copy2                 Database test_Copy2
SMOTestDb                  Database SMOTestDb
test                       Database test
test_Copy                  Database test_Copy
PK__test__3213E83F0DAF0CB0 Index    test_Copy2.test.PK__test__3213E83F0DAF0CB0
PK__test__3213E83F0DAF0CB0 Index    test_Copy.test.PK__test__3213E83F0DAF0CB0
PK__test__3213E83F0DAF0CB0 Index    test.test.PK__test__3213E83F0DAF0CB0
test                       Table    test.test
test                       Table    test_Copy2.test
test                       Table    test_Copy.test


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