Return SQL Query as Array in Powershell

Question

I have a SQL 2008 Ent server with the databases "DBOne", "DBTwo", "DBThree" on the server DEVSQLSRV.

Here is my Powershell script:

$DBNameList = (Invoke-SQLCmd -query "select Name from sysdatabases" -Server DEVSQLSRV)

This produces my desired list of database names as:

Name
-----
DBOne
DBTwo
DBThree

I has been my assumption that anything that is returned as a list is an Array in Powershell. However, when I then try this in Powershell:

$DBNameList -contains 'DBTwo'

It comes back has "False" instead of "True" which is leading me to believe that my list is not an actual array.

Any idea what I'm missing here?

Thanks so much!

Emo

Answer 1

I'd do this:

$DBNameList = @(Invoke-SQLCmd -query "select Name from sysdatabases" -Server DEVSQLSRV) | select-object -expand Name

That will give you an array of names.The option -contains should work fine.

Answer 2

What's missing from the original post is some type of conversion, from an object to an array.

Powershell outputs the result of $DBNameList because it kinda interprets the object. But if you need to manipulate this object and identify a specific item from it, this is the method I use:

$Itm = "DBTwo"
$DBNameList = @(Invoke-SQLCmd -query "select Name from sysdatabases" -Server DEVSQLSRV)
$NameList = @($DBNameList | select-object -ExpandProperty Name)
$Name = ($NameList.Split()).Contains($Itm)
Write-Output $Name

True

I have been looking for this myself for a while and finally worked it out, so I hope it helps someone else!