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

PowerShell for Searching Database Objects Part I

Here is ProwerShell 3.0 Script for searching database object based on their names. We will use AdventureWorksLT2012 database, and will look for SQL Server objects name containing “user” for table level objects only explicitly excluding Federations, because this throws an error. Basically we are going to achieve output below SQL query with ProwerShell Script.

USE [AdventureWorksLT2012]
SELECT
Type_Desc,
Name
FROM
sys.objects
WHERE
name LIKE '%user%'
AND TYPE NOT IN ('C','D','PK','F')

Now Open PowerShell console by Clicking  Start >> Accessories >> Windows PowerShell >> Windows PowerShell ISE and following code will help to take you to the destinations. Note that the following script will work only with PowerShell V3, because of the simplified Where cmdlet usage. If you are going to use this with PowerShell V2 then replace the Where syntax with the V2 variations.

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

#create array to store results
$ResultsArray = @()

#loop through all database SMO properties and search of objects that match search string
#note
$DBObject |
Get-Member -MemberType Property |
Where Definition -Like "*Smo*" |
Where Definition -NotLike "*Federation*" |
ForEach-Object{
 $ObjectType = $_.Name
 $DBObject.$ObjectType |
 Where Name -Like "*$SearchString*" |
 ForEach-Object{
 $Result = New-Object -Type PSObject -Prop @{
 "ObjectType"=$ObjectType.Replace("Microsoft.SqlServer.Management.Smo.", "")
 "ObjectName"=$_.Name}
 $ResultsArray += $Result
 }
}
#display results
$ResultsArray
#dump results to csv file
$OutputFileName = "C:\TempData\Results.csv"
$ResultsArray | Export-Csv -Path $OutputFileName -NoTypeInformation
#display file contents in NotePad
notepad $OutputFileName

In PowerShell for Searching Database Objects Part II, I will explain two simplest methods to search for database object with PowerShell V 3.0

Hope this helps.

Thanks

Sandip

Advertisements
  1. February 24, 2013 at 3:26 pm

    I cannot thank you enough for the article post. Really Cool.

  2. February 27, 2013 at 6:32 pm

    Superb! Two thumbs up! This is just everything what I was expecting to hear… Looking forward for any updates or new post that you might want to share…

  3. March 26, 2013 at 12:46 am

    I really enjoy the blog post.Really looking forward to read more. Much obliged.

  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: