The problem
In some situations I want to search through lots of objects to look for certain properties in SMO (SQL Server Management Objects)
This is also the case in this situation. I wanted to know all the different objects that had a property called “Schema”.
But what to do with all those different properties and methods we could look up. I mean, there are hundreds of objects in there and each of them have many methods and properties.
Getting the objects
|
1 2 3 4 5 |
$server = Connect-DbaInstance -SqlInstance localhost $db = $server.Databases['WWI'] $objects = $db | Get-Member |
Counting all the stuff we got back we have a count of 284. Going through each of the items is not going to work.
The first thing we have to do is filter out all the properties that are actual objects. We want to exclude all the properties that would return values like boolean, string etc.
Let’s change the object selection
|
1 |
$objects = $db | Get-Member | Where-Object { $_.MemberType -eq 'Property' -and $_.Definition -like 'Microsoft*' } |
That only leaves us with 82 objects which makes things a lot easier.
Now for the last part we’ll iterate through the objects and get the properties and check for the name “Schema”
|
1 2 3 4 5 6 7 8 9 |
foreach ($object in $objects) { $dbObjectProperties = $null $dbObjectProperties = $db.($object.Name) | Get-Member -ErrorAction SilentlyContinue | Where-Object Membertype -eq 'Property' | Select-Object Name if ($dbObjectProperties.Name -contains 'Schema') { $object.Name } } |
The result of the objects that have that property
- ExtendedStoredProcedures
- SecurityPolicies
- Sequences
- StoredProcedures
- Tables
- UserDefinedFunctions
- UserDefinedTableTypes
- Views
Cleaning up the script
I can’t help myself and I always want my scripts to be able to have parameters and have some error handling in them.
The script uses the Connect-DbaInstance command from dbatools.
The end result:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
[CmdLetBinding()] param( [string]$SqlInstance, [PScredential]$SqlCredential, [string]$Database, [string[]]$PropertyName ) if (-not $SqlInstance) { Write-Error "Please enter a SQL Server instance" return } if (-not $PropertyName) { Write-Error "Please enter a property to search for" return } try { $server = Connect-DbaInstance -SqlInstance $SqlInstance -SqlCredential $SqlCredential } catch { Write-Error "Could not connect to instance '$SqlInstance'" return } if (-not $Database) { Write-Verbose "Setting database to 'master'" $Database = 'master' } if ($Database -notin $server.Databases.Name) { Write-Warning "Database could not be found" return } $db = $server.Databases[$Database] $objects = $db | Get-Member | Where-Object { $_.MemberType -eq 'Property' -and $_.Definition -like 'Microsoft*' } foreach ($object in $objects) { Write-Verbose "Retrieving properties for $($object.Name)" $dbObjectProperties = $null $dbObjectProperties = $db.($object.Name) | Get-Member -ErrorAction SilentlyContinue | Where-Object Membertype -eq 'Property' | Select-Object Name if ($dbObjectProperties) { $results = Compare-Object -ReferenceObject $dbObjectProperties.Name -DifferenceObject $PropertyName -IncludeEqual if ($results.SideIndicator -contains "==") { $object.Name } } } |
Just run the command like this
|
1 |
.\Get-SMOObjectWithProperty.ps1 -SqlInstance localhost -PropertyName Schema |
Making it public
For anyone who wants to do something similar, here is the code
https://github.com/sanderstad/SMOProperties