6

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.

Improve this question
1
  • 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, 2012 at 14:19

1 Answer 1

Reset to default
7

Try this

$db.Discover() | select @{Name="Type";Expression={$_.GetType().Name}};
Improve this answer
12
  • 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".
    – user507
    May 25, 2012 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.
    – user507
    May 25, 2012 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, 2012 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.
    – user507
    May 25, 2012 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, 2012 at 14:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.