Advertisements
Home > Powershell, SQL Server > PowerShell for Searching Database Objects Part I‏I

PowerShell for Searching Database Objects Part I‏I

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.

PSSearchResults

Hope this helps.

Sandip

Advertisements
  1. February 27, 2013 at 6:45 pm

    Generally I do not read article on blogs, but I would like to say that this write-up very forced me to try and do so! Your writing style has been amazed me. Thanks, quite nice article.

  2. March 2, 2013 at 7:29 pm

    hello!,I love your writing so much! share we communicate more about your post on AOL? I need an expert in this house to resolve my problem. Maybe that’s you! Taking a look ahead to look you.

  3. March 9, 2013 at 10:14 am

    Thanks for the post I really appreciate it it was very useful

  4. March 20, 2013 at 10:14 pm

    nice post.added to my bookmarks cheers

  1. No trackbacks yet.

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

%d bloggers like this: