SQL Server SMO Method "Discover()" I think I just found the coolest thing every with SQL Server SMO and PowerShell. I was asked to get a script together to find the owner of every object in an instance of SQL Server. I found a script on Technet/MSDN SQL Server library but thought it would be a lot easier to get it with PowerShell and be able to hit all the instances on the network at once.

Well in playing around I came across this method within the Database class: Discover()

So if you use this code:


#Load SMO 
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null
$srv = New-Object 'Microsoft.SqlServer.Management.Smo.Server' "your instance name"

$db = $srv.Databases

$db.Discover()


When you run that last line you are going to see your console start scrolling text like it is on drugs, if you connect to an instance with a lot of databases on it.

Well I went to do  $db | gm  and it pretty much shows you every TypeName available within SMO objects.

Now my question. I can execute $db.Discover() | Select Name, Owner and it will start spitting out everything with a name and an owner value.

How could I also get the object type of the object being returned (like if it is a trigger, schema, table, etc)? I can't seem to figure that out so far.

EDIT

So far I find that it seems to only "discovery" things on the database level. Apparently somewhere in my console cache I had set  $db to something like $db = ($srv.Databases).Item("aDatabase"), cause this is the only way you can call the method. Still looking for a way to pull out Object Name, type, and Owner though.
Try this

$db.Discover() | select @{Name="Type";Expression={$_.GetType().Name}};