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 cod开发者_如何学运维e 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.

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 }

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.

How about:

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

How about just:

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

so, many years later I am looking into my SO stats and see this old question and figure that my powershell skills have grown a little since 2010.

The use-case has long gone but I think what I was trying to achieve is this:

foreach ($db in $SMOServer.databases | Where-Object status -eq 'normal') {
    $db.ExtendedProperties | Select-Object @{name = "DBName"; expression = {$db.Name}}, name, value
}

which gives results like this:

DBName                 Name                                 Value
------                 ----                                 -----
AdventureWorks2014     MS_Description                       AdventureWorks 2014 Sample OLTP Database
AdventureWorks2016     MS_Description                       AdventureWorks 2016 Sample OLTP Database