SQL Server PowerShell : Search for SQL Server Objects Using PowerShell

Written by belle
Posted June 4, 2009 at 12:49 am

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

                                                              
#============================================================
#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

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

Rating: 8.0/10 (6 votes cast)

Rating: 0 (from 0 votes)

SQL Server PowerShell : Search for SQL Server Objects Using PowerShell , 8.0 out of 10 based on 6 ratings

Be Sociable, Share!