Sql-server – SQL Server SMO Method “Discover()”

powershellsmosql server

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.

Best Answer

Try this

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

Related Solutions

Sql-server – How to script out push subscription creation at the subscriber

I'm going to outline a T-SQL way that uses most of what you already have.

Create a table to hold your publication information (for the solution that I have written, it's just the name of the publication and an identity column)
Create a table to hold your subscriber information (in mine, I have name of subscriber, subscriber database, identity column, and a column that references back to the publication)
When you want to create a new subscription, you put a new row in the subscriber table with the relevant information
In your "add subscription" script, do a left join between your table and syssubscriptions to find out what subscriptions are in your table but don't yet exist. Run a cursor over that info and for each subscription that doesn't exist, create it.
After you're done adding all of the subscriptions, call sp_startpublication_snapshot for each publication that has uninitialized subscribers.
Fin (aka "you're done").

Sql-server – missing some output

You cannot loop through objects in the way you have coded. Here's the working code:

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";

$ServerName = 'xyzabc123'
$DatabaseName = 'test1'
$TableName = 'main'
$TableSchemaName = 'dbo'

$Server = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerName
$Server.ConnectionContext.SqlExecutionModes = [Microsoft.SqlServer.Management.Common.SqlExecutionModes]::CaptureSql

$Database = $Server.Databases[$DatabaseName]

$Table = $Database.Tables[$TableName, $TableSchemaName]

$TablesToArchive = @($Table)
$Database.Tables | Select -ExpandProperty ForeignKeys | % {
    if($_.ReferencedTable -eq $TableName -and $_.ReferencedTableSchema -eq $TableSchemaName)
        { $TablesToArchive += $_.Parent }
}
# $TablesToArchive | % { $_.Name }

$ObjectsToArchive = ($TablesToArchive | Select -ExpandProperty Indexes) + ($TablesToArchive | Select -ExpandProperty ForeignKeys)
# $ObjectsToArchive | % { $_.Name }

$ObjectsToArchive | % {
    $_.Rename($_.Name + '_archive')
}
$RenameCommands = $Server.ConnectionContext.CapturedSql.Text
# $RenameCommands

For example, you were expecting

$Database.Tables.ForeignKeys

To give you a combined ForeignKeys collection made up of ForeignKeys of each Tables object. If you added the debug commands

# $ObjectsToArchive | % { $_.Name }

You would have been able to track it down very quickly.

Related Question