Share via


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"

Comments