Searching For SMO Objects With Certain Properties

Standard

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

$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

$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”

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

  1. ExtendedStoredProcedures
  2. SecurityPolicies
  3. Sequences
  4. StoredProcedures
  5. Tables
  6. UserDefinedFunctions
  7. UserDefinedTableTypes
  8. 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:

[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

.\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

 

Leave a Reply