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"}}, `
@{Name="FullName";Expression={[string]($_.Name)}}

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

[/ps]

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

3 thoughts on “SQL Server PowerShell : Search for SQL Server Objects Using PowerShell

  1. Tom Johnson July 8, 2009 / 5:51 pm

    Hey guys I’m a newbie to this SMO/Powershell environment and would like to know where can these scripts be executed/run from. I tried PS and it gives me an error on the first line. I first tried a .vbs/.js file type and this did not work. I also attempted to changed the file type to .sql and run this script from Microsoft SQL Server Management Studio Express to no avail.Can someone help get me started?

    Like

  2. John April 27, 2011 / 7:28 am

    try TM Field Finder to search in SQL Server schema. I tried and it works fine.

    Like

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 )

Google photo

You are commenting using your Google 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