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


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.

  • What other modules are you running? My profile is pretty bare. When I ran your code, I got a message saying that Smo.DatabaseCollection doesn't contain a method named Discover. – mbourgon May 30 '12 at 14:19

Try this

$db.Discover() | select @{Name="Type";Expression={$_.GetType().Name}};
  • I figured it would have to be some type of expression. As is works but it is returning the TypeName of ScriptNameOjbectBase or SqlSmoOjbect; so I dropped the BaseType in the expression and it at least returns a more readable typename like "Microsoft.SqlServer.Management.Smo.StoredProcedure". – Shawn Melton May 25 '12 at 14:19
  • So since everything does not have an owner, since Discover() also returns indexes, users, statistics I just need to figure out how to filter it down to only ownable objects. – Shawn Melton May 25 '12 at 14:20
  • 2
    Instead of the BaseType property, I'd suggest using the Name property. It's a bit more readable: $_.GetType().Name. – Thomas Stringer May 25 '12 at 14:22
  • What is the best way to filter out those objects that don't show an owner? I tried adding <code>? {$_.Owner -ne $NULL}</code> but it does not work or return what it should. – Shawn Melton May 25 '12 at 14:30
  • @ShawnMelton If you want to get all tables, for example, pipe your Select-Object cmdlet to this: Where-Object {$_.Type -eq "Table"}. Make sense? – Thomas Stringer May 25 '12 at 14:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.