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.