2

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.

Improve this question

5 Answers 5

Reset to default
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 }

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Pretty close, this error for each database: Get-ChildItem : Length cannot be less than zero. Parameter name: length At line:6 char:34 + Get-ChildItem | % { Get-ChildItem <<<< $("$_.Name\extendedproperties") | Sel ect DisplayName, Value } + CategoryInfo : NotSpecified: (:) [Get-ChildItem], ArgumentOutOf RangeException + FullyQualifiedErrorId : System.ArgumentOutOfRangeException,Microsoft.Pow erShell.Commands.GetChildItemCommand
can you explain the | % please? My help file doesnt return any results when I search for it...
Aah...so, the $_.Name isn't there. What do u see when you run Get-ChildItem at DRIVENAME:\Databases ? what is the name of the column that shows your database name? BTW, % is foreach-object. It is just a shorthand notation
yeah, got the | on its own, whats the % that follows it? Not all db's will have extended properties, I think thats where the problem is ...
gci after sl gives every property for every database. gci | select name gives name of every database
|
1

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.

Improve this answer

8 Comments

At line:6 char:47 + Get-ChildItem | foreach-object { if (Test-Path <<<< -path $("$.Name\extendedproperties\displayname")) { Get-ChildItem $("$.Name\extendedproperties") | Select Displ ayName, Value } } + CategoryInfo : InvalidData: (SQLSERVER:\sql\...ies\displayname:SqlPath) [Test-Path], GenericProviderException + FullyQualifiedErrorId : KeysNotMatching,Microsoft.PowerShell.Commands.TestPathCommand
tried adding "displayname" (see above) but original gives same error for each database
I am lost too..I need to look at the properties at this point..Is it possible to paste the output of Get-ChildItem DRIVERNAME:\Databases?
is there a way to only get one set, rather than for each db?
I am sorry I did not understand your last comment
|
1

How about:

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

5 Comments

(I should mention... I tested this on a system on which some, but not all, databases had extended properties)
This is crazy, I still get an error ::>> Get-ChildItem : Length cannot be less than zero. Parameter name: length At line:5 char:4 + dir <<<< sqlserver:\sql\myservername\myinstancename\databases*\ExtendedProperties* | select @{Name="Database";Expression={$_.Parent.Name}}, Name, Value + CategoryInfo : NotSpecified: (:) [Get-ChildItem], ArgumentOutOfRangeException + FullyQualifiedErrorId : System.ArgumentOutOfRangeException,Microsoft.PowerShell.Commands.GetChildItemCommand
You've missed a slash. Databases*\ExtendedProperties - with a slash after Databases, before the star.
Oh... SO is removing it maybe. It dropped it from my comment too.
not noticed that before. I had the slashes as per your answer (copy/paste). My error had same slashes but SO has dropped them as escape chars I think
0

How about just:

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

1 Comment

Hi Rob. Not all db's have extendedproperties :: Get-ChildItem : Length cannot be less than zero. Parameter name: length At line:12 char:4 + dir <<<< SQLSERVER:\SQL\Server\Instance\databases*\extendedproperties* | % {select $_.displayname, $_.value} + CategoryInfo : NotSpecified: (:) [Get-ChildItem], ArgumentOutOfRangeException + FullyQualifiedErrorId : System.ArgumentOutOfRangeException,Microsoft.PowerShell.Commands.GetChildItemCommand
0

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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.