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

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