Get SQL Server Database

PowerShell Help
1

The code below should only display non-system databases on a local SQL Server instance. I’m having multiple issues with the code, but will hopefully work through the others before asking questions. However, this one is throwing me off a bit. The code should only display one database, but instead also displays the “system” databases, i.e., master, model, msdb, and temdb. The SQL command being issued excludes those databases. However, when I add the “foreach” loop it displays all of them.

Here’s the code:

$machine = "$env:COMPUTERNAME"
$server  = New-Object Microsoft.Sqlserver.Management.Smo.Server("$machine")
$server.ConnectionContext.LoginSecure=$true

$database  = $server.Databases["master"]
$command   = "SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');"
$dataSet   = $database.ExecuteWithResults($command)
$dataTable = $dataSet.Tables[0]

$output = foreach ($row in $dataTable)
{
    Get-SqlDatabase -ServerInstance $machine | Select name,status,size,spaceavailable,compat.level,owner
}

$output | ft

If I stop the code before the “output” variable, and run everything prior it will display all non-system databases.

Any insight as to why all of them are being displayed from the loop? Thx!

2

If you don’t specify the database name, won’t Get-SQLDatabase just return all the databases?

Essentially, you’re running the same command for every row in your datatable, regardless of what that row actually contains. I suspect you want to be doing something like

$output = foreach ($row in $dataTable)
{
    Get-SqlDatabase -ServerInstance $machine -Name $row.name | Select name,status,size,spaceavailable,compat.level,owner
}
2 Likes