Big Data, Data Analytics and Data Science | BI-BIGDATA.com

In my last article we seen PowerShell for Searching Database Objects Part I. If you look at the actual PowerShell Script in that article it looks very complicated. In this article you will find another two ways to search databases objects with PowerShell with simple(understandable) script.

We can iterate through the objects is by using the EnumObjects method of the SMO database variable $DBObject

#import SQL Server module
Import-Module SQLPS -DisableNameChecking

#your SQL Server Instance Name
$SQLInstanceName = "SQL2012"
$Server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $SQLInstanceName

#provide your database name where you want to search
$DatabaseName = "AdventureWorksLT2012"
$DBObject = $Server.Databases[$DatabaseName]

#search string
$SearchString = "customer"

$DBObject.EnumObjects()|
Where Name -Like "*$SearchString*"|
Select DatabaseObjectTypes,Name|
Format-Table -AutoSize

Another way to achieve this is little bit longer and less flexible but we gets what we want. In this script we can browse object properties one by one to match selection criteria as show below

#import SQL Server module
Import-Module SQLPS -DisableNameChecking

#your SQL Server Instance Name
$SQLInstanceName = "SQL2012"
$Server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $SQLInstanceName

#provide your database name where you want to search
$DatabaseName = "AdventureWorksLT2012"
$DBObject = $Server.Databases[$DatabaseName]

#search string
$SearchString = "customer"

#long version is to enumerate explicity each object type
$DBObject.Tables|Where Name -Like "*$SearchString*"
$DBObject.StoredProcedures|Where Name -Like "*$SearchString*"
$DBObject.Triggers|Where Name -Like "*$SearchString*"
$DBObject.UserDefinedFunctions|Where Name -Like "*$SearchString*"

After execution both the scripts you will get below output.

Hope this helps.

Sandip