Returning an SQL query as an array in Powershell

Question

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

Here is my Powershell script:

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

This leads to my desired list of database names as:

 Name ----- DBOne DBTwo DBThree 

I figured that everything returned as a list is an array in Powershell. However, when I try this in Powershell:

 $DBNameList -contains 'DBTwo' 

The response returns "False" instead of "True", which makes me think that my list is not an actual array.

Any idea what I'm missing here?

Many thanks!

Emo

Answer 1

I would do this:

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

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

Answer 2

What is missing in the original message is some kind of conversion, from object to array.

Powershell outputs the result from $ DBNameList because it simply interprets the object. But if you need to manipulate this object and identify a specific element 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 searched for it myself for a while and finally worked it out, so I hope this helps someone else!

Answer 3

The Name header offers a single object with the Name property, which is an array.

I consider initializing an empty PS array:

 $DBNameList = (Invoke-SQLCmd -query "select Name from sysdatabases" -Server DEVSQLSRV) [Array]$DbNames = @() $DBNameList.Name | ForEach-Object {$DbNames += $_} $DbNames -contains "DBTwo" 

Any luck?

Answer 4

Your code ...

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

... gives you Datarows back ..

You can check it with

 $DBNameList | Get-Member 

You can also see that there is a property called "Name".

If you want to check if one of the datarows in your $ DBNameList contains the name "DBTwo", you need to write the following:

 $DBNameList.Name -contains 'DBTwo' 

Answer 5

In case someone else was here because he was terrified, he had to manually enter the name of each property in the response of the DataRow object to get several columns in the array, do not be afraid, there is a convenient property called "ItemArray", which provides what you need.

 (Invoke-SQLCmd -query "select Name from sysdatabases").ItemArray -contains 'DBTwo' 

true

There are many good answers here that solve this particular OP problem, but when the list of columns gets longer, it makes things a lot easier.

 (Invoke-SQLCmd -query "select DBID,Name,Version from sysdatabases")[0].ItemArray -join ',' 

1, master, 852

Answer 6

Still very new to Powershell (less than two weeks): I suggest you try this if your query contains multiple columns and rows ... Multidimensional arrays. This was my first attempt at this, and after checking the network, given that I could not find a simple direct solution, I eventually wrote my own solution. Here is a complete set of sample code for experimentation and use.

---

A complete set of code examples below ....

  ############################################################################################# # RDSago # [e-mail] # 09/20/2014 ############################################################################################# # # Capturing database size information from a collection of servers # and returning that back to an array that can be used to populate # a SQL table that can be used for monitoring database growth remotely. # RDSago, [e-mail] # # Note, SQL data retrieved in this manner, does not have to be parsed # before it is consumed and used elsewhere, just like any array you have defined. # The data only needs to be addressed by its ".identityname" captured in the # array $queryResults (shown below). # ############################################################################################ ############################################################################################# # T-SQL for creating table to hold data returned # # CREATE TABLE [dba].[tbl_dbfilesize]( # [ServerNameInstance] [varchar](20) NULL, # [DatabaseName] [varchar](30) NULL, # [DataFileSizeMB] [numeric](20, 0) NULL, # [LogFileSizeMB] [numeric](20, 0) NULL, # [TotalDatabaseSizeMB] [numeric](20, 0) NULL, # [CollectionDate] [date] NULL # ) ON [PRIMARY] ############################################################################################# Try { #define your connection points # first create an array that will hold the server/instance name of the servers you wish to audit # the first sever assumes a named instance, the second a default instance name. $SourceServerName = @("ServerName01/InstanceName", "ServerName02", "ServerName03") # Server you will retrieve data from #next define the server connection for where you will write your data back to $TargetServerInstance = "TaretServerName" # define your sql query that will be used to pull data from SQL on the Source Server $qryDatabaseInfo = " SELECT @@ServerName as ServerNameInstance, DB.name as DatabaseName, SUM(CASE WHEN type = 0 THEN MF.size * 8 / 1024 ELSE 0 END) AS DataFileSizeMB, SUM(CASE WHEN type = 1 THEN MF.size * 8 / 1024 ELSE 0 END) AS LogFileSizeMB, SUM(CASE WHEN type = 1 THEN MF.size * 8 / 1024 ELSE 0 END) + SUM(CASE WHEN type = 0 THEN MF.size * 8 / 1024 ELSE 0 END) AS TotalDatabaseSizeMB FROM sys.master_files MF JOIN sys.databases DB ON DB.database_id = MF.database_id GROUP BY DB.name ORDER BY DB.NAME ASC " #Loop through all the servers you wish to audit ForEach ($SourceServerName in $SourceServerNames) { #execute query to pull data from server into an array $queryResults = @(Invoke-SQLCmd -query $qryDatabaseInfo -Server $SourceServerInstance) # Next, construct your insert statement from data in your $queryresults array. Foreach ($queryResult in $queryResults) { $query = " Insert Into [DBS_AUDIT_SERVERS].[dba].[tbl_dbfilesize] ([ServerNameInstance], [DatabaseName], [DataFileSizeMB], [LogFileSizeMB], [TotalDatabaseSizeMB], [CollectionDate]) Values (" + "'" + $SourceServerInstance + "'," + "'" + $queryResult.DatabaseName + "'," + "'" + $queryResult.DataFileSizeMB + "'," + "'" + $queryResult.LogFileSizeMB + "'," + "'" + $queryResult.TotalDatabaseSizeMB + "'," + "'" + $Date + "'" + ")" "" #execute insert statement for sql Invoke-Sqlcmd -Query $query -ServerInstance $TargetServerInstance } } } Catch [Exception] { $ErrorMessage = $_.Exception.Message Write-Host $ErrorMessage } Finally { Write-Host "Completed Successfully" } Return 0;