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

Related posts:

1. How to Search for Columns in SQL Server ...
2. SQL Server PowerShell : How to Audit Your SQL Server Instance Properties Using PowerShell and SMO ...
3. SQL Server PowerShell : How to Backup SQL Server Databases Using SMO and PowerShell ...
4. SQL Server PowerShell : How to Restore SQL Server Databases Using SMO and PowerShell ...
5. How to script out your database objects (Database Publishing Wizard) ...
6. SQL Server PowerShell : How to List SQL Server Services using PowerShell ...