Tuesday, December 05, 2006

PowerShell, SQL & SMO

On november 30th I showed how to access a SQL Server using ADO. Todays script does excatly the same, but uses SMO instead of ADO:

function ShowAllDB_SMO {
    [void][reflection.assembly]::LoadWithPartialName( "Microsoft.SqlServer.ConnectionInfo" );
    [void][reflection.assembly]::LoadWithPartialName( "Microsoft.SqlServer.SmoEnum" );
    [void][reflection.assembly]::LoadWithPartialName( "Microsoft.SqlServer.Smo" );
    $server = new-object( 'Microsoft.SqlServer.Management.Smo.Server' ) $args[0]
    foreach ($database in $server.databases) {
        $temp0 = $database.ID
        $temp1 = $database.Name
        Write-Output "$temp0 ; $temp1"
    }
}

To use the function:

ShowAllDBs_SMO [server or server\instance]

1 comment:

Anonymous said...

Hi, very good example and it also shows (in comparison with the ADO example earlier) how slow SMO is. Best to avoid SMO!