List all Databases from your SQL Server via PowerShell

  • Article

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"

Comments