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