PowerShell script to list items in collection

Question

Hi, I'm new to PowerShell and am trying to query against my SQL server. I get the idea of creating a new-psdrive and then navigating to databases etc and have a line of code as

$dbs = (get-childitem sqlserver:\sql\SERVER\INSTANCE\databases)

when I pipe the $dbs to a foreach, how would I get results of a collection of the database object? I am trying to read the extendedproperties of my test database.

This single query gives the results I want repeated for each database.

set-location DRIVENAME:\databases\beagle_test\extendedproperties get-childitem | select displayname, value

any help very much appreciated.

Answer 1

I dont have SQL server handy to try this. Let me know the result

Set-Location DRIVENAME:\Databases

Get-ChildItem | % { Get-ChildItem $("$_.Name\extendedproperties") | Select DisplayName, Value }

Answer 2

Try this

Set-Location DRIVENAME:\Databases

Get-ChildItem | foreach-object { if (Test-Path $("$.Name\extendedproperties")) { Get-ChildItem $("$.Name\extendedproperties") | Select DisplayName, Value } }

The second line here is a single statement. What I am doing is to check if Extendedproperties exist and then get child item.

Answer 3

How about just:

dir SQLSERVER:\SQL\Server\Instance\databases\*\extendedproperties\* | % {select $_.displayname, $_.value}

Answer 4

How about:

dir sqlserver:\sql\ServerName\InstanceName\Databases\*\ExtendedProperties\* | 
  select @{Name="Database";Expression={$_.Parent.Name}}, Name, Value