Wednesday, May 8, 2013

List all Databases from your SQL Server via PowerShell

I've created this script, because some guys wanted to know which DBs are available.
It can be used to e.g. mount some unmounted contentdatabases.

-----------

function ListAllSQLDBs {param ( $DatabaseServer)
if ($DatabaseServer -eq $null)

    $DatabaseName=   "master"
    $QueryString = "EXEC sp_databases"

    $SQLDBs = New-Object system.Data.DataTable
    $col1 = New-Object system.Data.DataColumn DBName
    $SQLDBs.columns.add($col1)
    function ADD_TO_SQL_DB_TABLE
    {param ( $DBName)
        $newRow = $SQLDBs.NewRow()
     $newRow.DBName = $DBName
        $SQLDBs.rows.add($newRow)
     }

    [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
    $conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($DatabaseServer)
    $conn.DatabaseName = $DatabaseName
    $r = $conn.ExecuteReader($QueryString)
    While ($r.Read())
    {
       $Name = $r.GetValue(0);
       ADD_TO_SQL_DB_TABLE -DBName $Name
    }
    $r.Close()
    Write-Output $SQLDBs
}

#functioncall
ListAllSQLDBs -DatabaseServer "mydbserver"

1 comment:

  1. did you even try this script champ? I was gonna until I spotted a number of glaring syntax errors by eyeball. For starters, why don't you look up the syntax for a simple powershell if statement. It's no wonder you've had 50k page views and no comments.

    ReplyDelete