• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Unable to Select-Object

T

Tom Moreau

#1
I am running the following code:


[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$smo = new-object Microsoft.SqlServer.Management.Smo.Server ".\SQL2005"

$smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema -eq
"Sales"} | Select-Object Indexes | foreach {Write-Host $_.name}

The last statement writes an empty line for each table in the Tables
collection. (I'm using Write-Host just for debugging. I actually want to
manipulate each index.) When I run the following:

$smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema -eq
"Sales"} | Select-Object Indexes

.... I get the following output:

Indexes
-------
{PK_ContactCreditCard_ContactID_CreditCardID}
{IX_CountryRegionCurrency_CurrencyCode,
PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode}
{AK_CreditCard_CardNumber, PK_CreditCard_CreditCardID}
{AK_Currency_Name, PK_Currency_CurrencyCode}
{AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode,
PK_CurrencyRate_CurrencyRateID}
....


How do I pick up the object within the foreach loop?
--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
 

My Computer

B

Brandon Shell [MVP]

#2
It seems to me it is just an array so this should work

$smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema -eq "Sales"}
| Select-Object Indexes | foreach {Write-Host $_}

Brandon Shell
---------------
Blog: http://www.bsonposh.com/
PSH Scripts Project: www.codeplex.com/psobject

TM> I am running the following code:
TM>
TM> [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServe
TM> r.Smo") $smo = new-object Microsoft.SqlServer.Management.Smo.Server
TM> ".\SQL2005"
TM>
TM> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM> -eq "Sales"} | Select-Object Indexes | foreach {Write-Host $_.name}
TM>
TM> The last statement writes an empty line for each table in the Tables
TM> collection. (I'm using Write-Host just for debugging. I actually
TM> want to manipulate each index.) When I run the following:
TM>
TM> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM> -eq "Sales"} | Select-Object Indexes
TM>
TM> ... I get the following output:
TM>
TM> Indexes
TM> -------
TM> {PK_ContactCreditCard_ContactID_CreditCardID}
TM> {IX_CountryRegionCurrency_CurrencyCode,
TM> PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode}
TM> {AK_CreditCard_CardNumber, PK_CreditCard_CreditCardID}
TM> {AK_Currency_Name, PK_Currency_CurrencyCode}
TM> {AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode,
TM> PK_CurrencyRate_CurrencyRateID}
TM> ...
TM> How do I pick up the object within the foreach loop?
TM>
TM> ----------------------------------------------------
TM> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM> SQL Server MVP
TM> Toronto, ON Canada
TM> https://mvp.support.microsoft.com/profile/Tom.Moreau
 

My Computer

B

Brandon Shell [MVP]

#3
What do you get back when you do this.

$smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema -eq "Sales"}|
Select-Object Indexes

Brandon Shell
---------------
Blog: http://www.bsonposh.com/
PSH Scripts Project: www.codeplex.com/psobject

TM> No, I get this back:
TM>
TM> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>
TM> It really is supposed to be a collection of Index objects.
TM>
TM> ----------------------------------------------------
TM> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM> SQL Server MVP
TM> Toronto, ON Canada
TM> https://mvp.support.microsoft.com/profile/Tom.Moreau
TM> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in message
TM> news:29d4f646387e8ca27da71057e80@xxxxxx
TM> It seems to me it is just an array so this should work
TM> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM> -eq
TM> "Sales"}
TM> | Select-Object Indexes | foreach {Write-Host $_}
TM> Brandon Shell
TM> ---------------
TM> Blog: http://www.bsonposh.com/
TM> PSH Scripts Project: www.codeplex.com/psobject
TM>> I am running the following code:
TM>>
TM>> [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServ
TM>> e r.Smo") $smo = new-object
TM>> Microsoft.SqlServer.Management.Smo.Server ".\SQL2005"
TM>>
TM>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM>> -eq "Sales"} | Select-Object Indexes | foreach {Write-Host $_.name}
TM>>
TM>> The last statement writes an empty line for each table in the
TM>> Tables collection. (I'm using Write-Host just for debugging. I
TM>> actually want to manipulate each index.) When I run the following:
TM>>
TM>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM>> -eq "Sales"} | Select-Object Indexes
TM>>
TM>> ... I get the following output:
TM>>
TM>> Indexes
TM>> -------
TM>> {PK_ContactCreditCard_ContactID_CreditCardID}
TM>> {IX_CountryRegionCurrency_CurrencyCode,
TM>> PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode}
TM>> {AK_CreditCard_CardNumber, PK_CreditCard_CreditCardID}
TM>> {AK_Currency_Name, PK_Currency_CurrencyCode}
TM>> {AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode,
TM>> PK_CurrencyRate_CurrencyRateID}
TM>> ...
TM>> How do I pick up the object within the foreach loop?
TM>> ----------------------------------------------------
TM>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>> SQL Server MVP
TM>> Toronto, ON Canada
TM>> https://mvp.support.microsoft.com/profile/Tom.Moreau
 

My Computer

T

Tom Moreau

#4
No, I get this back:

@{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
@{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
@{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
@{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
@{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
@{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}

It really is supposed to be a collection of Index objects.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in message
news:29d4f646387e8ca27da71057e80@xxxxxx
It seems to me it is just an array so this should work

$smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema -eq
"Sales"}
| Select-Object Indexes | foreach {Write-Host $_}

Brandon Shell
---------------
Blog: http://www.bsonposh.com/
PSH Scripts Project: www.codeplex.com/psobject

TM> I am running the following code:
TM>
TM> [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServe
TM> r.Smo") $smo = new-object Microsoft.SqlServer.Management.Smo.Server
TM> ".\SQL2005"
TM>
TM> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM> -eq "Sales"} | Select-Object Indexes | foreach {Write-Host $_.name}
TM>
TM> The last statement writes an empty line for each table in the Tables
TM> collection. (I'm using Write-Host just for debugging. I actually
TM> want to manipulate each index.) When I run the following:
TM>
TM> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM> -eq "Sales"} | Select-Object Indexes
TM>
TM> ... I get the following output:
TM>
TM> Indexes
TM> -------
TM> {PK_ContactCreditCard_ContactID_CreditCardID}
TM> {IX_CountryRegionCurrency_CurrencyCode,
TM> PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode}
TM> {AK_CreditCard_CardNumber, PK_CreditCard_CreditCardID}
TM> {AK_Currency_Name, PK_Currency_CurrencyCode}
TM> {AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode,
TM> PK_CurrencyRate_CurrencyRateID}
TM> ...
TM> How do I pick up the object within the foreach loop?
TM>
TM> ----------------------------------------------------
TM> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM> SQL Server MVP
TM> Toronto, ON Canada
TM> https://mvp.support.microsoft.com/profile/Tom.Moreau
 

My Computer

T

Tom Moreau

#5
That was in my original post:

TM>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM>> -eq "Sales"} | Select-Object Indexes
TM>>
TM>> ... I get the following output:
TM>>
TM>> Indexes
TM>> -------
TM>> {PK_ContactCreditCard_ContactID_CreditCardID}
TM>> {IX_CountryRegionCurrency_CurrencyCode,
TM>> PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode}
TM>> {AK_CreditCard_CardNumber, PK_CreditCard_CreditCardID}
TM>> {AK_Currency_Name, PK_Currency_CurrencyCode}
TM>> {AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode,
TM>> PK_CurrencyRate_CurrencyRateID}


--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in message
news:29d4f64638818ca27dc0111014c@xxxxxx
What do you get back when you do this.

$smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema -eq
"Sales"}|
Select-Object Indexes

Brandon Shell
---------------
Blog: http://www.bsonposh.com/
PSH Scripts Project: www.codeplex.com/psobject

TM> No, I get this back:
TM>
TM> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>
TM> It really is supposed to be a collection of Index objects.
TM>
TM> ----------------------------------------------------
TM> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM> SQL Server MVP
TM> Toronto, ON Canada
TM> https://mvp.support.microsoft.com/profile/Tom.Moreau
TM> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in message
TM> news:29d4f646387e8ca27da71057e80@xxxxxx
TM> It seems to me it is just an array so this should work
TM> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM> -eq
TM> "Sales"}
TM> | Select-Object Indexes | foreach {Write-Host $_}
TM> Brandon Shell
TM> ---------------
TM> Blog: http://www.bsonposh.com/
TM> PSH Scripts Project: www.codeplex.com/psobject
TM>> I am running the following code:
TM>>
TM>> [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServ
TM>> e r.Smo") $smo = new-object
TM>> Microsoft.SqlServer.Management.Smo.Server ".\SQL2005"
TM>>
TM>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM>> -eq "Sales"} | Select-Object Indexes | foreach {Write-Host $_.name}
TM>>
TM>> The last statement writes an empty line for each table in the
TM>> Tables collection. (I'm using Write-Host just for debugging. I
TM>> actually want to manipulate each index.) When I run the following:
TM>>
TM>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM>> -eq "Sales"} | Select-Object Indexes
TM>>
TM>> ... I get the following output:
TM>>
TM>> Indexes
TM>> -------
TM>> {PK_ContactCreditCard_ContactID_CreditCardID}
TM>> {IX_CountryRegionCurrency_CurrencyCode,
TM>> PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode}
TM>> {AK_CreditCard_CardNumber, PK_CreditCard_CreditCardID}
TM>> {AK_Currency_Name, PK_Currency_CurrencyCode}
TM>> {AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode,
TM>> PK_CurrencyRate_CurrencyRateID}
TM>> ...
TM>> How do I pick up the object within the foreach loop?
TM>> ----------------------------------------------------
TM>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>> SQL Server MVP
TM>> Toronto, ON Canada
TM>> https://mvp.support.microsoft.com/profile/Tom.Moreau
 

My Computer

B

Brandon Shell [MVP]

#6
Ok.. I think the problem is that Indexes returns and array of These
http://technet.microsoft.com/zh-cn/...r.management.smo.indexcollection_members.aspx
which contain these
http://technet.microsoft.com/zh-cn/library/microsoft.sqlserver.management.smo.index.aspx

While you could do this on oneline.. I think it is unreadable so I would
do this ( sorry I cant test im doing this in head :S )

$Indexes = $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
-eq "Sales"}| Select-Object Indexes
foreach($Index in $Indexes)
{
$Index | %{$_.Name}
}

Brandon Shell
---------------
Blog: http://www.bsonposh.com/
PSH Scripts Project: www.codeplex.com/psobject

TM> That was in my original post:
TM>
TM>>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM>>> -eq "Sales"} | Select-Object Indexes
TM>>>
TM>>> ... I get the following output:
TM>>>
TM>>> Indexes
TM>>> -------
TM>>> {PK_ContactCreditCard_ContactID_CreditCardID}
TM>>> {IX_CountryRegionCurrency_CurrencyCode,
TM>>> PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode}
TM>>> {AK_CreditCard_CardNumber, PK_CreditCard_CreditCardID}
TM>>> {AK_Currency_Name, PK_Currency_CurrencyCode}
TM>>> {AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode,
TM>>> PK_CurrencyRate_CurrencyRateID}
TM> ----------------------------------------------------
TM> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM> SQL Server MVP
TM> Toronto, ON Canada
TM> https://mvp.support.microsoft.com/profile/Tom.Moreau
TM> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in message
TM> news:29d4f64638818ca27dc0111014c@xxxxxx
TM> What do you get back when you do this.
TM> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM> -eq
TM> "Sales"}|
TM> Select-Object Indexes
TM> Brandon Shell
TM> ---------------
TM> Blog: http://www.bsonposh.com/
TM> PSH Scripts Project: www.codeplex.com/psobject
TM>> No, I get this back:
TM>>
TM>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>
TM>> It really is supposed to be a collection of Index objects.
TM>>
TM>> ----------------------------------------------------
TM>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>> SQL Server MVP
TM>> Toronto, ON Canada
TM>> https://mvp.support.microsoft.com/profile/Tom.Moreau
TM>> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in message
TM>> news:29d4f646387e8ca27da71057e80@xxxxxx
TM>> It seems to me it is just an array so this should work
TM>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM>> -eq
TM>> "Sales"}
TM>> | Select-Object Indexes | foreach {Write-Host $_}
TM>> Brandon Shell
TM>> ---------------
TM>> Blog: http://www.bsonposh.com/
TM>> PSH Scripts Project: www.codeplex.com/psobject
TM>>> I am running the following code:
TM>>>
TM>>> [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlSer
TM>>> v e r.Smo") $smo = new-object
TM>>> Microsoft.SqlServer.Management.Smo.Server ".\SQL2005"
TM>>>
TM>>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM>>> -eq "Sales"} | Select-Object Indexes | foreach {Write-Host
TM>>> $_.name}
TM>>>
TM>>> The last statement writes an empty line for each table in the
TM>>> Tables collection. (I'm using Write-Host just for debugging. I
TM>>> actually want to manipulate each index.) When I run the
TM>>> following:
TM>>>
TM>>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM>>> -eq "Sales"} | Select-Object Indexes
TM>>>
TM>>> ... I get the following output:
TM>>>
TM>>> Indexes
TM>>> -------
TM>>> {PK_ContactCreditCard_ContactID_CreditCardID}
TM>>> {IX_CountryRegionCurrency_CurrencyCode,
TM>>> PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode}
TM>>> {AK_CreditCard_CardNumber, PK_CreditCard_CreditCardID}
TM>>> {AK_Currency_Name, PK_Currency_CurrencyCode}
TM>>> {AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode,
TM>>> PK_CurrencyRate_CurrencyRateID}
TM>>> ...
TM>>> How do I pick up the object within the foreach loop?
TM>>> ----------------------------------------------------
TM>>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>>> SQL Server MVP
TM>>> Toronto, ON Canada
TM>>> https://mvp.support.microsoft.com/profile/Tom.Moreau
 

My Computer

T

Tom Moreau

#7
I get nothing back from this. I still don't get why foreach just isn't
getting the object.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in message
news:29d4f646388c8ca27e0af10ce1d@xxxxxx
Ok.. I think the problem is that Indexes returns and array of These
http://technet.microsoft.com/zh-cn/...r.management.smo.indexcollection_members.aspx
which contain these
http://technet.microsoft.com/zh-cn/library/microsoft.sqlserver.management.smo.index.aspx

While you could do this on oneline.. I think it is unreadable so I would
do this ( sorry I cant test im doing this in head :S )

$Indexes = $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
-eq "Sales"}| Select-Object Indexes
foreach($Index in $Indexes)
{
$Index | %{$_.Name}
}

Brandon Shell
---------------
Blog: http://www.bsonposh.com/
PSH Scripts Project: www.codeplex.com/psobject

TM> That was in my original post:
TM>
TM>>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM>>> -eq "Sales"} | Select-Object Indexes
TM>>>
TM>>> ... I get the following output:
TM>>>
TM>>> Indexes
TM>>> -------
TM>>> {PK_ContactCreditCard_ContactID_CreditCardID}
TM>>> {IX_CountryRegionCurrency_CurrencyCode,
TM>>> PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode}
TM>>> {AK_CreditCard_CardNumber, PK_CreditCard_CreditCardID}
TM>>> {AK_Currency_Name, PK_Currency_CurrencyCode}
TM>>> {AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode,
TM>>> PK_CurrencyRate_CurrencyRateID}
TM> ----------------------------------------------------
TM> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM> SQL Server MVP
TM> Toronto, ON Canada
TM> https://mvp.support.microsoft.com/profile/Tom.Moreau
TM> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in message
TM> news:29d4f64638818ca27dc0111014c@xxxxxx
TM> What do you get back when you do this.
TM> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM> -eq
TM> "Sales"}|
TM> Select-Object Indexes
TM> Brandon Shell
TM> ---------------
TM> Blog: http://www.bsonposh.com/
TM> PSH Scripts Project: www.codeplex.com/psobject
TM>> No, I get this back:
TM>>
TM>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>
TM>> It really is supposed to be a collection of Index objects.
TM>>
TM>> ----------------------------------------------------
TM>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>> SQL Server MVP
TM>> Toronto, ON Canada
TM>> https://mvp.support.microsoft.com/profile/Tom.Moreau
TM>> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in message
TM>> news:29d4f646387e8ca27da71057e80@xxxxxx
TM>> It seems to me it is just an array so this should work
TM>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM>> -eq
TM>> "Sales"}
TM>> | Select-Object Indexes | foreach {Write-Host $_}
TM>> Brandon Shell
TM>> ---------------
TM>> Blog: http://www.bsonposh.com/
TM>> PSH Scripts Project: www.codeplex.com/psobject
TM>>> I am running the following code:
TM>>>
TM>>> [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlSer
TM>>> v e r.Smo") $smo = new-object
TM>>> Microsoft.SqlServer.Management.Smo.Server ".\SQL2005"
TM>>>
TM>>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM>>> -eq "Sales"} | Select-Object Indexes | foreach {Write-Host
TM>>> $_.name}
TM>>>
TM>>> The last statement writes an empty line for each table in the
TM>>> Tables collection. (I'm using Write-Host just for debugging. I
TM>>> actually want to manipulate each index.) When I run the
TM>>> following:
TM>>>
TM>>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM>>> -eq "Sales"} | Select-Object Indexes
TM>>>
TM>>> ... I get the following output:
TM>>>
TM>>> Indexes
TM>>> -------
TM>>> {PK_ContactCreditCard_ContactID_CreditCardID}
TM>>> {IX_CountryRegionCurrency_CurrencyCode,
TM>>> PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode}
TM>>> {AK_CreditCard_CardNumber, PK_CreditCard_CreditCardID}
TM>>> {AK_Currency_Name, PK_Currency_CurrencyCode}
TM>>> {AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode,
TM>>> PK_CurrencyRate_CurrencyRateID}
TM>>> ...
TM>>> How do I pick up the object within the foreach loop?
TM>>> ----------------------------------------------------
TM>>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>>> SQL Server MVP
TM>>> Toronto, ON Canada
TM>>> https://mvp.support.microsoft.com/profile/Tom.Moreau
 

My Computer

B

Brandon Shell [MVP]

#8
That is because Indexes is an array of arrays

$smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema -eq "Sales"}|
Select-Object Indexes | %{$_} | %{$_.name}

Perhaps I can explain what I think is happening
* Select-Object Indexes # this passes just the Collection of Index Collections
down the pipe
* | %{$_} # This takes each Index Collection and passes
it down the pipe
* | %{$_.name} # This should get each Index in the Index Collection
and Ouput just the Name

Brandon Shell
---------------
Blog: http://www.bsonposh.com/
PSH Scripts Project: www.codeplex.com/psobject

TM> I get nothing back from this. I still don't get why foreach just
TM> isn't getting the object.
TM>
TM> ----------------------------------------------------
TM> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM> SQL Server MVP
TM> Toronto, ON Canada
TM> https://mvp.support.microsoft.com/profile/Tom.Moreau
TM> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in message
TM>
TM> news:29d4f646388c8ca27e0af10ce1d@xxxxxx
TM>
TM> Ok.. I think the problem is that Indexes returns and array of These
TM>
TM> http://technet.microsoft.com/zh-cn/library/microsoft.sqlserver.manag
TM> ement.smo.indexcollection_members.aspx
TM>
TM> which contain these
TM>
TM> http://technet.microsoft.com/zh-cn/library/microsoft.sqlserver.manag
TM> ement.smo.index.aspx
TM>
TM> While you could do this on oneline.. I think it is unreadable so I
TM> would do this ( sorry I cant test im doing this in head :S )
TM>
TM> $Indexes = $smo.Databases["AdventureWorks"].Tables | Where-Object
TM> {$_.Schema
TM> -eq "Sales"}| Select-Object Indexes
TM> foreach($Index in $Indexes)
TM> {
TM> $Index | %{$_.Name}
TM> }
TM> Brandon Shell
TM> ---------------
TM> Blog: http://www.bsonposh.com/
TM> PSH Scripts Project: www.codeplex.com/psobject
TM>> That was in my original post:
TM>>
TM>>>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM>>>> -eq "Sales"} | Select-Object Indexes
TM>>>>
TM>>>> ... I get the following output:
TM>>>>
TM>>>> Indexes
TM>>>> -------
TM>>>> {PK_ContactCreditCard_ContactID_CreditCardID}
TM>>>> {IX_CountryRegionCurrency_CurrencyCode,
TM>>>> PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode}
TM>>>> {AK_CreditCard_CardNumber, PK_CreditCard_CreditCardID}
TM>>>> {AK_Currency_Name, PK_Currency_CurrencyCode}
TM>>>> {AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode
TM>>>> ,
TM>>>> PK_CurrencyRate_CurrencyRateID}
TM>> ----------------------------------------------------
TM>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>> SQL Server MVP
TM>> Toronto, ON Canada
TM>> https://mvp.support.microsoft.com/profile/Tom.Moreau
TM>> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in message
TM>> news:29d4f64638818ca27dc0111014c@xxxxxx
TM>> What do you get back when you do this.
TM>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM>> -eq
TM>> "Sales"}|
TM>> Select-Object Indexes
TM>> Brandon Shell
TM>> ---------------
TM>> Blog: http://www.bsonposh.com/
TM>> PSH Scripts Project: www.codeplex.com/psobject
TM>>> No, I get this back:
TM>>>
TM>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>>
TM>>> It really is supposed to be a collection of Index objects.
TM>>>
TM>>> ----------------------------------------------------
TM>>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>>> SQL Server MVP
TM>>> Toronto, ON Canada
TM>>> https://mvp.support.microsoft.com/profile/Tom.Moreau
TM>>> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in message
TM>>> news:29d4f646387e8ca27da71057e80@xxxxxx
TM>>> It seems to me it is just an array so this should work
TM>>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM>>> -eq
TM>>> "Sales"}
TM>>> | Select-Object Indexes | foreach {Write-Host $_}
TM>>> Brandon Shell
TM>>> ---------------
TM>>> Blog: http://www.bsonposh.com/
TM>>> PSH Scripts Project: www.codeplex.com/psobject
TM>>>> I am running the following code:
TM>>>>
TM>>>> [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlSe
TM>>>> r v e r.Smo") $smo = new-object
TM>>>> Microsoft.SqlServer.Management.Smo.Server ".\SQL2005"
TM>>>>
TM>>>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM>>>> -eq "Sales"} | Select-Object Indexes | foreach {Write-Host
TM>>>> $_.name}
TM>>>>
TM>>>> The last statement writes an empty line for each table in the
TM>>>> Tables collection. (I'm using Write-Host just for debugging. I
TM>>>> actually want to manipulate each index.) When I run the
TM>>>> following:
TM>>>>
TM>>>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM>>>> -eq "Sales"} | Select-Object Indexes
TM>>>>
TM>>>> ... I get the following output:
TM>>>>
TM>>>> Indexes
TM>>>> -------
TM>>>> {PK_ContactCreditCard_ContactID_CreditCardID}
TM>>>> {IX_CountryRegionCurrency_CurrencyCode,
TM>>>> PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode}
TM>>>> {AK_CreditCard_CardNumber, PK_CreditCard_CreditCardID}
TM>>>> {AK_Currency_Name, PK_Currency_CurrencyCode}
TM>>>> {AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode
TM>>>> ,
TM>>>> PK_CurrencyRate_CurrencyRateID}
TM>>>> ...
TM>>>> How do I pick up the object within the foreach loop?
TM>>>> ----------------------------------------------------
TM>>>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>>>> SQL Server MVP
TM>>>> Toronto, ON Canada
TM>>>> https://mvp.support.microsoft.com/profile/Tom.Moreau
 

My Computer

T

Tom Moreau

#9
I got nothing back from that, too. I have made some progress, though.
Here' what I have so far:

$smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema -eq
"Sales"} | foreach {$Table = $_.Name; Write-Host $Table; foreach ($Index in
$_.Indexes) {Write-host "`t" $Index.Name "`t" $Index.SpaceUsed}}

When I add other methods in the second foreach, I'm not seeing evidence that
they were actually sent to SQL Server, but that may be more of a SMO issue
than PowerShell.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in message
news:29d4f64638948ca27e45cab9041@xxxxxx
That is because Indexes is an array of arrays

$smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema -eq
"Sales"}|
Select-Object Indexes | %{$_} | %{$_.name}

Perhaps I can explain what I think is happening
* Select-Object Indexes # this passes just the Collection of Index
Collections
down the pipe
* | %{$_} # This takes each Index Collection and passes
it down the pipe
* | %{$_.name} # This should get each Index in the Index
Collection
and Ouput just the Name

Brandon Shell
---------------
Blog: http://www.bsonposh.com/
PSH Scripts Project: www.codeplex.com/psobject

TM> I get nothing back from this. I still don't get why foreach just
TM> isn't getting the object.
TM>
TM> ----------------------------------------------------
TM> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM> SQL Server MVP
TM> Toronto, ON Canada
TM> https://mvp.support.microsoft.com/profile/Tom.Moreau
TM> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in message
TM>
TM> news:29d4f646388c8ca27e0af10ce1d@xxxxxx
TM>
TM> Ok.. I think the problem is that Indexes returns and array of These
TM>
TM> http://technet.microsoft.com/zh-cn/library/microsoft.sqlserver.manag
TM> ement.smo.indexcollection_members.aspx
TM>
TM> which contain these
TM>
TM> http://technet.microsoft.com/zh-cn/library/microsoft.sqlserver.manag
TM> ement.smo.index.aspx
TM>
TM> While you could do this on oneline.. I think it is unreadable so I
TM> would do this ( sorry I cant test im doing this in head :S )
TM>
TM> $Indexes = $smo.Databases["AdventureWorks"].Tables | Where-Object
TM> {$_.Schema
TM> -eq "Sales"}| Select-Object Indexes
TM> foreach($Index in $Indexes)
TM> {
TM> $Index | %{$_.Name}
TM> }
TM> Brandon Shell
TM> ---------------
TM> Blog: http://www.bsonposh.com/
TM> PSH Scripts Project: www.codeplex.com/psobject
TM>> That was in my original post:
TM>>
TM>>>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM>>>> -eq "Sales"} | Select-Object Indexes
TM>>>>
TM>>>> ... I get the following output:
TM>>>>
TM>>>> Indexes
TM>>>> -------
TM>>>> {PK_ContactCreditCard_ContactID_CreditCardID}
TM>>>> {IX_CountryRegionCurrency_CurrencyCode,
TM>>>> PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode}
TM>>>> {AK_CreditCard_CardNumber, PK_CreditCard_CreditCardID}
TM>>>> {AK_Currency_Name, PK_Currency_CurrencyCode}
TM>>>> {AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode
TM>>>> ,
TM>>>> PK_CurrencyRate_CurrencyRateID}
TM>> ----------------------------------------------------
TM>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>> SQL Server MVP
TM>> Toronto, ON Canada
TM>> https://mvp.support.microsoft.com/profile/Tom.Moreau
TM>> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in message
TM>> news:29d4f64638818ca27dc0111014c@xxxxxx
TM>> What do you get back when you do this.
TM>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM>> -eq
TM>> "Sales"}|
TM>> Select-Object Indexes
TM>> Brandon Shell
TM>> ---------------
TM>> Blog: http://www.bsonposh.com/
TM>> PSH Scripts Project: www.codeplex.com/psobject
TM>>> No, I get this back:
TM>>>
TM>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>>
TM>>> It really is supposed to be a collection of Index objects.
TM>>>
TM>>> ----------------------------------------------------
TM>>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>>> SQL Server MVP
TM>>> Toronto, ON Canada
TM>>> https://mvp.support.microsoft.com/profile/Tom.Moreau
TM>>> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in message
TM>>> news:29d4f646387e8ca27da71057e80@xxxxxx
TM>>> It seems to me it is just an array so this should work
TM>>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM>>> -eq
TM>>> "Sales"}
TM>>> | Select-Object Indexes | foreach {Write-Host $_}
TM>>> Brandon Shell
TM>>> ---------------
TM>>> Blog: http://www.bsonposh.com/
TM>>> PSH Scripts Project: www.codeplex.com/psobject
TM>>>> I am running the following code:
TM>>>>
TM>>>> [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlSe
TM>>>> r v e r.Smo") $smo = new-object
TM>>>> Microsoft.SqlServer.Management.Smo.Server ".\SQL2005"
TM>>>>
TM>>>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM>>>> -eq "Sales"} | Select-Object Indexes | foreach {Write-Host
TM>>>> $_.name}
TM>>>>
TM>>>> The last statement writes an empty line for each table in the
TM>>>> Tables collection. (I'm using Write-Host just for debugging. I
TM>>>> actually want to manipulate each index.) When I run the
TM>>>> following:
TM>>>>
TM>>>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM>>>> -eq "Sales"} | Select-Object Indexes
TM>>>>
TM>>>> ... I get the following output:
TM>>>>
TM>>>> Indexes
TM>>>> -------
TM>>>> {PK_ContactCreditCard_ContactID_CreditCardID}
TM>>>> {IX_CountryRegionCurrency_CurrencyCode,
TM>>>> PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode}
TM>>>> {AK_CreditCard_CardNumber, PK_CreditCard_CreditCardID}
TM>>>> {AK_Currency_Name, PK_Currency_CurrencyCode}
TM>>>> {AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode
TM>>>> ,
TM>>>> PK_CurrencyRate_CurrencyRateID}
TM>>>> ...
TM>>>> How do I pick up the object within the foreach loop?
TM>>>> ----------------------------------------------------
TM>>>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>>>> SQL Server MVP
TM>>>> Toronto, ON Canada
TM>>>> https://mvp.support.microsoft.com/profile/Tom.Moreau
 

My Computer

B

Brandon Shell [MVP]

#10
I wish I could have been more help :S Glad you got it working :)


Brandon Shell
---------------
Blog: http://www.bsonposh.com/
PSH Scripts Project: www.codeplex.com/psobject

TM> I got nothing back from that, too. I have made some progress,
TM> though. Here' what I have so far:
TM>
TM> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM> -eq "Sales"} | foreach {$Table = $_.Name; Write-Host $Table; foreach
TM> ($Index in $_.Indexes) {Write-host "`t" $Index.Name "`t"
TM> $Index.SpaceUsed}}
TM>
TM> When I add other methods in the second foreach, I'm not seeing
TM> evidence that they were actually sent to SQL Server, but that may be
TM> more of a SMO issue than PowerShell.
TM>
TM> ----------------------------------------------------
TM> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM> SQL Server MVP
TM> Toronto, ON Canada
TM> https://mvp.support.microsoft.com/profile/Tom.Moreau
TM> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in message
TM> news:29d4f64638948ca27e45cab9041@xxxxxx
TM> That is because Indexes is an array of arrays
TM> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM> -eq
TM> "Sales"}|
TM> Select-Object Indexes | %{$_} | %{$_.name}
TM> Perhaps I can explain what I think is happening
TM> * Select-Object Indexes # this passes just the Collection of Index
TM> Collections
TM> down the pipe
TM> * | %{$_} # This takes each Index Collection and
TM> passes
TM> it down the pipe
TM> * | %{$_.name} # This should get each Index in the Index
TM> Collection
TM> and Ouput just the Name
TM> Brandon Shell
TM> ---------------
TM> Blog: http://www.bsonposh.com/
TM> PSH Scripts Project: www.codeplex.com/psobject
TM>> I get nothing back from this. I still don't get why foreach just
TM>> isn't getting the object.
TM>>
TM>> ----------------------------------------------------
TM>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>> SQL Server MVP
TM>> Toronto, ON Canada
TM>> https://mvp.support.microsoft.com/profile/Tom.Moreau
TM>> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in message
TM>> news:29d4f646388c8ca27e0af10ce1d@xxxxxx
TM>>
TM>> Ok.. I think the problem is that Indexes returns and array of These
TM>>
TM>> http://technet.microsoft.com/zh-cn/library/microsoft.sqlserver.mana
TM>> g ement.smo.indexcollection_members.aspx
TM>>
TM>> which contain these
TM>>
TM>> http://technet.microsoft.com/zh-cn/library/microsoft.sqlserver.mana
TM>> g ement.smo.index.aspx
TM>>
TM>> While you could do this on oneline.. I think it is unreadable so I
TM>> would do this ( sorry I cant test im doing this in head :S )
TM>>
TM>> $Indexes = $smo.Databases["AdventureWorks"].Tables | Where-Object
TM>> {$_.Schema
TM>> -eq "Sales"}| Select-Object Indexes
TM>> foreach($Index in $Indexes)
TM>> {
TM>> $Index | %{$_.Name}
TM>> }
TM>> Brandon Shell
TM>> ---------------
TM>> Blog: http://www.bsonposh.com/
TM>> PSH Scripts Project: www.codeplex.com/psobject
TM>>> That was in my original post:
TM>>>
TM>>>>> $smo.Databases["AdventureWorks"].Tables | Where-Object
TM>>>>> {$_.Schema -eq "Sales"} | Select-Object Indexes
TM>>>>>
TM>>>>> ... I get the following output:
TM>>>>>
TM>>>>> Indexes
TM>>>>> -------
TM>>>>> {PK_ContactCreditCard_ContactID_CreditCardID}
TM>>>>> {IX_CountryRegionCurrency_CurrencyCode,
TM>>>>> PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode}
TM>>>>> {AK_CreditCard_CardNumber, PK_CreditCard_CreditCardID}
TM>>>>> {AK_Currency_Name, PK_Currency_CurrencyCode}
TM>>>>> {AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCod
TM>>>>> e
TM>>>>> ,
TM>>>>> PK_CurrencyRate_CurrencyRateID}
TM>>> ----------------------------------------------------
TM>>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>>> SQL Server MVP
TM>>> Toronto, ON Canada
TM>>> https://mvp.support.microsoft.com/profile/Tom.Moreau
TM>>> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in message
TM>>> news:29d4f64638818ca27dc0111014c@xxxxxx
TM>>> What do you get back when you do this.
TM>>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM>>> -eq
TM>>> "Sales"}|
TM>>> Select-Object Indexes
TM>>> Brandon Shell
TM>>> ---------------
TM>>> Blog: http://www.bsonposh.com/
TM>>> PSH Scripts Project: www.codeplex.com/psobject
TM>>>> No, I get this back:
TM>>>>
TM>>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>>>
TM>>>> It really is supposed to be a collection of Index objects.
TM>>>>
TM>>>> ----------------------------------------------------
TM>>>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>>>> SQL Server MVP
TM>>>> Toronto, ON Canada
TM>>>> https://mvp.support.microsoft.com/profile/Tom.Moreau
TM>>>> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in
TM>>>> message
TM>>>> news:29d4f646387e8ca27da71057e80@xxxxxx
TM>>>> It seems to me it is just an array so this should work
TM>>>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM>>>> -eq
TM>>>> "Sales"}
TM>>>> | Select-Object Indexes | foreach {Write-Host $_}
TM>>>> Brandon Shell
TM>>>> ---------------
TM>>>> Blog: http://www.bsonposh.com/
TM>>>> PSH Scripts Project: www.codeplex.com/psobject
TM>>>>> I am running the following code:
TM>>>>>
TM>>>>> [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlS
TM>>>>> e r v e r.Smo") $smo = new-object
TM>>>>> Microsoft.SqlServer.Management.Smo.Server ".\SQL2005"
TM>>>>>
TM>>>>> $smo.Databases["AdventureWorks"].Tables | Where-Object
TM>>>>> {$_.Schema -eq "Sales"} | Select-Object Indexes | foreach
TM>>>>> {Write-Host $_.name}
TM>>>>>
TM>>>>> The last statement writes an empty line for each table in the
TM>>>>> Tables collection. (I'm using Write-Host just for debugging. I
TM>>>>> actually want to manipulate each index.) When I run the
TM>>>>> following:
TM>>>>>
TM>>>>> $smo.Databases["AdventureWorks"].Tables | Where-Object
TM>>>>> {$_.Schema -eq "Sales"} | Select-Object Indexes
TM>>>>>
TM>>>>> ... I get the following output:
TM>>>>>
TM>>>>> Indexes
TM>>>>> -------
TM>>>>> {PK_ContactCreditCard_ContactID_CreditCardID}
TM>>>>> {IX_CountryRegionCurrency_CurrencyCode,
TM>>>>> PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode}
TM>>>>> {AK_CreditCard_CardNumber, PK_CreditCard_CreditCardID}
TM>>>>> {AK_Currency_Name, PK_Currency_CurrencyCode}
TM>>>>> {AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCod
TM>>>>> e
TM>>>>> ,
TM>>>>> PK_CurrencyRate_CurrencyRateID}
TM>>>>> ...
TM>>>>> How do I pick up the object within the foreach loop?
TM>>>>> ----------------------------------------------------
TM>>>>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>>>>> SQL Server MVP
TM>>>>> Toronto, ON Canada
TM>>>>> https://mvp.support.microsoft.com/profile/Tom.Moreau
 

My Computer

T

Tom Moreau

#11
Well, you got me to thinking and that's half the battle. I have lots of
Perl and VBScript and this PS stuff is new to me. It's fun, though. The
ability to keep pipelining stuff looks pretty kewl to me.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in message
news:29d4f64638b48ca27fe5ea42989@xxxxxx
I wish I could have been more help :S Glad you got it working :)


Brandon Shell
---------------
Blog: http://www.bsonposh.com/
PSH Scripts Project: www.codeplex.com/psobject

TM> I got nothing back from that, too. I have made some progress,
TM> though. Here' what I have so far:
TM>
TM> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM> -eq "Sales"} | foreach {$Table = $_.Name; Write-Host $Table; foreach
TM> ($Index in $_.Indexes) {Write-host "`t" $Index.Name "`t"
TM> $Index.SpaceUsed}}
TM>
TM> When I add other methods in the second foreach, I'm not seeing
TM> evidence that they were actually sent to SQL Server, but that may be
TM> more of a SMO issue than PowerShell.
TM>
TM> ----------------------------------------------------
TM> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM> SQL Server MVP
TM> Toronto, ON Canada
TM> https://mvp.support.microsoft.com/profile/Tom.Moreau
TM> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in message
TM> news:29d4f64638948ca27e45cab9041@xxxxxx
TM> That is because Indexes is an array of arrays
TM> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM> -eq
TM> "Sales"}|
TM> Select-Object Indexes | %{$_} | %{$_.name}
TM> Perhaps I can explain what I think is happening
TM> * Select-Object Indexes # this passes just the Collection of Index
TM> Collections
TM> down the pipe
TM> * | %{$_} # This takes each Index Collection and
TM> passes
TM> it down the pipe
TM> * | %{$_.name} # This should get each Index in the Index
TM> Collection
TM> and Ouput just the Name
TM> Brandon Shell
TM> ---------------
TM> Blog: http://www.bsonposh.com/
TM> PSH Scripts Project: www.codeplex.com/psobject
TM>> I get nothing back from this. I still don't get why foreach just
TM>> isn't getting the object.
TM>>
TM>> ----------------------------------------------------
TM>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>> SQL Server MVP
TM>> Toronto, ON Canada
TM>> https://mvp.support.microsoft.com/profile/Tom.Moreau
TM>> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in message
TM>> news:29d4f646388c8ca27e0af10ce1d@xxxxxx
TM>>
TM>> Ok.. I think the problem is that Indexes returns and array of These
TM>>
TM>> http://technet.microsoft.com/zh-cn/library/microsoft.sqlserver.mana
TM>> g ement.smo.indexcollection_members.aspx
TM>>
TM>> which contain these
TM>>
TM>> http://technet.microsoft.com/zh-cn/library/microsoft.sqlserver.mana
TM>> g ement.smo.index.aspx
TM>>
TM>> While you could do this on oneline.. I think it is unreadable so I
TM>> would do this ( sorry I cant test im doing this in head :S )
TM>>
TM>> $Indexes = $smo.Databases["AdventureWorks"].Tables | Where-Object
TM>> {$_.Schema
TM>> -eq "Sales"}| Select-Object Indexes
TM>> foreach($Index in $Indexes)
TM>> {
TM>> $Index | %{$_.Name}
TM>> }
TM>> Brandon Shell
TM>> ---------------
TM>> Blog: http://www.bsonposh.com/
TM>> PSH Scripts Project: www.codeplex.com/psobject
TM>>> That was in my original post:
TM>>>
TM>>>>> $smo.Databases["AdventureWorks"].Tables | Where-Object
TM>>>>> {$_.Schema -eq "Sales"} | Select-Object Indexes
TM>>>>>
TM>>>>> ... I get the following output:
TM>>>>>
TM>>>>> Indexes
TM>>>>> -------
TM>>>>> {PK_ContactCreditCard_ContactID_CreditCardID}
TM>>>>> {IX_CountryRegionCurrency_CurrencyCode,
TM>>>>> PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode}
TM>>>>> {AK_CreditCard_CardNumber, PK_CreditCard_CreditCardID}
TM>>>>> {AK_Currency_Name, PK_Currency_CurrencyCode}
TM>>>>> {AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCod
TM>>>>> e
TM>>>>> ,
TM>>>>> PK_CurrencyRate_CurrencyRateID}
TM>>> ----------------------------------------------------
TM>>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>>> SQL Server MVP
TM>>> Toronto, ON Canada
TM>>> https://mvp.support.microsoft.com/profile/Tom.Moreau
TM>>> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in message
TM>>> news:29d4f64638818ca27dc0111014c@xxxxxx
TM>>> What do you get back when you do this.
TM>>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM>>> -eq
TM>>> "Sales"}|
TM>>> Select-Object Indexes
TM>>> Brandon Shell
TM>>> ---------------
TM>>> Blog: http://www.bsonposh.com/
TM>>> PSH Scripts Project: www.codeplex.com/psobject
TM>>>> No, I get this back:
TM>>>>
TM>>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>>>
TM>>>> It really is supposed to be a collection of Index objects.
TM>>>>
TM>>>> ----------------------------------------------------
TM>>>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>>>> SQL Server MVP
TM>>>> Toronto, ON Canada
TM>>>> https://mvp.support.microsoft.com/profile/Tom.Moreau
TM>>>> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in
TM>>>> message
TM>>>> news:29d4f646387e8ca27da71057e80@xxxxxx
TM>>>> It seems to me it is just an array so this should work
TM>>>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM>>>> -eq
TM>>>> "Sales"}
TM>>>> | Select-Object Indexes | foreach {Write-Host $_}
TM>>>> Brandon Shell
TM>>>> ---------------
TM>>>> Blog: http://www.bsonposh.com/
TM>>>> PSH Scripts Project: www.codeplex.com/psobject
TM>>>>> I am running the following code:
TM>>>>>
TM>>>>> [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlS
TM>>>>> e r v e r.Smo") $smo = new-object
TM>>>>> Microsoft.SqlServer.Management.Smo.Server ".\SQL2005"
TM>>>>>
TM>>>>> $smo.Databases["AdventureWorks"].Tables | Where-Object
TM>>>>> {$_.Schema -eq "Sales"} | Select-Object Indexes | foreach
TM>>>>> {Write-Host $_.name}
TM>>>>>
TM>>>>> The last statement writes an empty line for each table in the
TM>>>>> Tables collection. (I'm using Write-Host just for debugging. I
TM>>>>> actually want to manipulate each index.) When I run the
TM>>>>> following:
TM>>>>>
TM>>>>> $smo.Databases["AdventureWorks"].Tables | Where-Object
TM>>>>> {$_.Schema -eq "Sales"} | Select-Object Indexes
TM>>>>>
TM>>>>> ... I get the following output:
TM>>>>>
TM>>>>> Indexes
TM>>>>> -------
TM>>>>> {PK_ContactCreditCard_ContactID_CreditCardID}
TM>>>>> {IX_CountryRegionCurrency_CurrencyCode,
TM>>>>> PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode}
TM>>>>> {AK_CreditCard_CardNumber, PK_CreditCard_CreditCardID}
TM>>>>> {AK_Currency_Name, PK_Currency_CurrencyCode}
TM>>>>> {AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCod
TM>>>>> e
TM>>>>> ,
TM>>>>> PK_CurrencyRate_CurrencyRateID}
TM>>>>> ...
TM>>>>> How do I pick up the object within the foreach loop?
TM>>>>> ----------------------------------------------------
TM>>>>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>>>>> SQL Server MVP
TM>>>>> Toronto, ON Canada
TM>>>>> https://mvp.support.microsoft.com/profile/Tom.Moreau
 

My Computer

S

Shay Levi

#12
Hi Thomas,

For what its worth, I've been able to run this on my local db server:

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$smo = new-object Microsoft.SqlServer.Management.Smo.Server ".\sqlexpress"
$smo.Databases["test"].Tables["tbl1"].indexes | select name

Name
----
myIndex



-----
Shay Levi
$cript Fanatic
http://scriptolog.blogspot.com

> Well, you got me to thinking and that's half the battle. I have lots
> of Perl and VBScript and this PS stuff is new to me. It's fun,
> though. The ability to keep pipelining stuff looks pretty kewl to me.
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in message
> news:29d4f64638b48ca27fe5ea42989@xxxxxx
> I wish I could have been more help :S Glad you got it working :)
> Brandon Shell
> ---------------
> Blog: http://www.bsonposh.com/
> PSH Scripts Project: www.codeplex.com/psobject
TM>> I got nothing back from that, too. I have made some progress,
TM>> though. Here' what I have so far:
TM>>
TM>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM>> -eq "Sales"} | foreach {$Table = $_.Name; Write-Host $Table;
TM>> foreach ($Index in $_.Indexes) {Write-host "`t" $Index.Name "`t"
TM>> $Index.SpaceUsed}}
TM>>
TM>> When I add other methods in the second foreach, I'm not seeing
TM>> evidence that they were actually sent to SQL Server, but that may
TM>> be more of a SMO issue than PowerShell.
TM>>
TM>> ----------------------------------------------------
TM>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>> SQL Server MVP
TM>> Toronto, ON Canada
TM>> https://mvp.support.microsoft.com/profile/Tom.Moreau
TM>> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in message
TM>> news:29d4f64638948ca27e45cab9041@xxxxxx
TM>> That is because Indexes is an array of arrays
TM>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM>> -eq
TM>> "Sales"}|
TM>> Select-Object Indexes | %{$_} | %{$_.name}
TM>> Perhaps I can explain what I think is happening
TM>> * Select-Object Indexes # this passes just the Collection of Index
TM>> Collections
TM>> down the pipe
TM>> * | %{$_} # This takes each Index Collection and
TM>> passes
TM>> it down the pipe
TM>> * | %{$_.name} # This should get each Index in the Index
TM>> Collection
TM>> and Ouput just the Name
TM>> Brandon Shell
TM>> ---------------
TM>> Blog: http://www.bsonposh.com/
TM>> PSH Scripts Project: www.codeplex.com/psobject
TM>>> I get nothing back from this. I still don't get why foreach just
TM>>> isn't getting the object.
TM>>>
TM>>> ----------------------------------------------------
TM>>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>>> SQL Server MVP
TM>>> Toronto, ON Canada
TM>>> https://mvp.support.microsoft.com/profile/Tom.Moreau
TM>>> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in message
TM>>> news:29d4f646388c8ca27e0af10ce1d@xxxxxx
TM>>> Ok.. I think the problem is that Indexes returns and array of
TM>>> These
TM>>>
TM>>> http://technet.microsoft.com/zh-cn/library/microsoft.sqlserver.man
TM>>> a g ement.smo.indexcollection_members.aspx
TM>>>
TM>>> which contain these
TM>>>
TM>>> http://technet.microsoft.com/zh-cn/library/microsoft.sqlserver.man
TM>>> a g ement.smo.index.aspx
TM>>>
TM>>> While you could do this on oneline.. I think it is unreadable so I
TM>>> would do this ( sorry I cant test im doing this in head :S )
TM>>>
TM>>> $Indexes = $smo.Databases["AdventureWorks"].Tables | Where-Object
TM>>> {$_.Schema
TM>>> -eq "Sales"}| Select-Object Indexes
TM>>> foreach($Index in $Indexes)
TM>>> {
TM>>> $Index | %{$_.Name}
TM>>> }
TM>>> Brandon Shell
TM>>> ---------------
TM>>> Blog: http://www.bsonposh.com/
TM>>> PSH Scripts Project: www.codeplex.com/psobject
TM>>>> That was in my original post:
TM>>>>
TM>>>>>> $smo.Databases["AdventureWorks"].Tables | Where-Object
TM>>>>>> {$_.Schema -eq "Sales"} | Select-Object Indexes
TM>>>>>>
TM>>>>>> ... I get the following output:
TM>>>>>>
TM>>>>>> Indexes
TM>>>>>> -------
TM>>>>>> {PK_ContactCreditCard_ContactID_CreditCardID}
TM>>>>>> {IX_CountryRegionCurrency_CurrencyCode,
TM>>>>>> PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode}
TM>>>>>> {AK_CreditCard_CardNumber, PK_CreditCard_CreditCardID}
TM>>>>>> {AK_Currency_Name, PK_Currency_CurrencyCode}
TM>>>>>> {AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCo
TM>>>>>> d
TM>>>>>> e
TM>>>>>> ,
TM>>>>>> PK_CurrencyRate_CurrencyRateID}
TM>>>> ----------------------------------------------------
TM>>>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>>>> SQL Server MVP
TM>>>> Toronto, ON Canada
TM>>>> https://mvp.support.microsoft.com/profile/Tom.Moreau
TM>>>> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in
TM>>>> message
TM>>>> news:29d4f64638818ca27dc0111014c@xxxxxx
TM>>>> What do you get back when you do this.
TM>>>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM>>>> -eq
TM>>>> "Sales"}|
TM>>>> Select-Object Indexes
TM>>>> Brandon Shell
TM>>>> ---------------
TM>>>> Blog: http://www.bsonposh.com/
TM>>>> PSH Scripts Project: www.codeplex.com/psobject
TM>>>>> No, I get this back:
TM>>>>>
TM>>>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>>>>
TM>>>>> It really is supposed to be a collection of Index objects.
TM>>>>>
TM>>>>> ----------------------------------------------------
TM>>>>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>>>>> SQL Server MVP
TM>>>>> Toronto, ON Canada
TM>>>>> https://mvp.support.microsoft.com/profile/Tom.Moreau
TM>>>>> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in
TM>>>>> message
TM>>>>> news:29d4f646387e8ca27da71057e80@xxxxxx
TM>>>>> It seems to me it is just an array so this should work
TM>>>>> $smo.Databases["AdventureWorks"].Tables | Where-Object
TM>>>>> {$_.Schema
TM>>>>> -eq
TM>>>>> "Sales"}
TM>>>>> | Select-Object Indexes | foreach {Write-Host $_}
TM>>>>> Brandon Shell
TM>>>>> ---------------
TM>>>>> Blog: http://www.bsonposh.com/
TM>>>>> PSH Scripts Project: www.codeplex.com/psobject
TM>>>>>> I am running the following code:
TM>>>>>>
TM>>>>>> [void][reflection.assembly]::LoadWithPartialName("Microsoft.Sql
TM>>>>>> S e r v e r.Smo") $smo = new-object
TM>>>>>> Microsoft.SqlServer.Management.Smo.Server ".\SQL2005"
TM>>>>>>
TM>>>>>> $smo.Databases["AdventureWorks"].Tables | Where-Object
TM>>>>>> {$_.Schema -eq "Sales"} | Select-Object Indexes | foreach
TM>>>>>> {Write-Host $_.name}
TM>>>>>>
TM>>>>>> The last statement writes an empty line for each table in the
TM>>>>>> Tables collection. (I'm using Write-Host just for debugging.
TM>>>>>> I actually want to manipulate each index.) When I run the
TM>>>>>> following:
TM>>>>>>
TM>>>>>> $smo.Databases["AdventureWorks"].Tables | Where-Object
TM>>>>>> {$_.Schema -eq "Sales"} | Select-Object Indexes
TM>>>>>>
TM>>>>>> ... I get the following output:
TM>>>>>>
TM>>>>>> Indexes
TM>>>>>> -------
TM>>>>>> {PK_ContactCreditCard_ContactID_CreditCardID}
TM>>>>>> {IX_CountryRegionCurrency_CurrencyCode,
TM>>>>>> PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode}
TM>>>>>> {AK_CreditCard_CardNumber, PK_CreditCard_CreditCardID}
TM>>>>>> {AK_Currency_Name, PK_Currency_CurrencyCode}
TM>>>>>> {AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCo
TM>>>>>> d
TM>>>>>> e
TM>>>>>> ,
TM>>>>>> PK_CurrencyRate_CurrencyRateID}
TM>>>>>> ...
TM>>>>>> How do I pick up the object within the foreach loop?
TM>>>>>> ----------------------------------------------------
TM>>>>>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>>>>>> SQL Server MVP
TM>>>>>> Toronto, ON Canada
TM>>>>>> https://mvp.support.microsoft.com/profile/Tom.Moreau
 

My Computer

S

Shay Levi

#13
Also, select and foreach has different outputs:

PS > $smo.Databases["msdb"].Tables["msdbms"].indexes

(...)
Name : pk_MSdbms
SpaceUsed : 8
(...)



# foreach shows that there is a value for the spaceused
PS > $smo.Databases["msdb"].Tables["msdbms"].indexes | foreach {$_.name;$_.spaceused}
pk_MSdbms
8

# select doesn't show it
PS > $smo.Databases["msdb"].Tables["msdbms"].indexes | select name,spaceused

Name
----
pk_MSdbms


The same with:

PS > $smo.Databases["msdb"].Tables["msdbms"].indexes | format-list name,space*

Name : pk_MSdbms
SpaceUsed : 8


PS > $smo.Databases["msdb"].Tables["msdbms"].indexes | format-table name,space*

Name
----
pk_MSdbms



Got to dig some more...


-----
Shay Levi
$cript Fanatic
http://scriptolog.blogspot.com

> Hi Thomas,
>
> For what its worth, I've been able to run this on my local db server:
>
> [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.
> Smo") $smo = new-object Microsoft.SqlServer.Management.Smo.Server
> ".\sqlexpress" $smo.Databases["test"].Tables["tbl1"].indexes | select
> name
>
> Name
> ----
> myIndex
> -----
> Shay Levi
> $cript Fanatic
> http://scriptolog.blogspot.com

>> Well, you got me to thinking and that's half the battle. I have lots
>> of Perl and VBScript and this PS stuff is new to me. It's fun,
>> though. The ability to keep pipelining stuff looks pretty kewl to
>> me.
>>
>> ----------------------------------------------------
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in message
>> news:29d4f64638b48ca27fe5ea42989@xxxxxx
>> I wish I could have been more help :S Glad you got it working :)
>> Brandon Shell
>> ---------------
>> Blog: http://www.bsonposh.com/
>> PSH Scripts Project: www.codeplex.com/psobject
TM>>> I got nothing back from that, too. I have made some progress,
TM>>> though. Here' what I have so far:
TM>>>
TM>>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM>>> -eq "Sales"} | foreach {$Table = $_.Name; Write-Host $Table;
TM>>> foreach ($Index in $_.Indexes) {Write-host "`t" $Index.Name "`t"
TM>>> $Index.SpaceUsed}}
TM>>>
TM>>> When I add other methods in the second foreach, I'm not seeing
TM>>> evidence that they were actually sent to SQL Server, but that may
TM>>> be more of a SMO issue than PowerShell.
TM>>>
TM>>> ----------------------------------------------------
TM>>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>>> SQL Server MVP
TM>>> Toronto, ON Canada
TM>>> https://mvp.support.microsoft.com/profile/Tom.Moreau
TM>>> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in message
TM>>> news:29d4f64638948ca27e45cab9041@xxxxxx
TM>>> That is because Indexes is an array of arrays
TM>>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM>>> -eq
TM>>> "Sales"}|
TM>>> Select-Object Indexes | %{$_} | %{$_.name}
TM>>> Perhaps I can explain what I think is happening
TM>>> * Select-Object Indexes # this passes just the Collection of Index
TM>>> Collections
TM>>> down the pipe
TM>>> * | %{$_} # This takes each Index Collection and
TM>>> passes
TM>>> it down the pipe
TM>>> * | %{$_.name} # This should get each Index in the Index
TM>>> Collection
TM>>> and Ouput just the Name
TM>>> Brandon Shell
TM>>> ---------------
TM>>> Blog: http://www.bsonposh.com/
TM>>> PSH Scripts Project: www.codeplex.com/psobject
TM>>>> I get nothing back from this. I still don't get why foreach just
TM>>>> isn't getting the object.
TM>>>>
TM>>>> ----------------------------------------------------
TM>>>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>>>> SQL Server MVP
TM>>>> Toronto, ON Canada
TM>>>> https://mvp.support.microsoft.com/profile/Tom.Moreau
TM>>>> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in
TM>>>> message
TM>>>> news:29d4f646388c8ca27e0af10ce1d@xxxxxx
TM>>>> Ok.. I think the problem is that Indexes returns and array of
TM>>>> These
TM>>>> http://technet.microsoft.com/zh-cn/library/microsoft.sqlserver.ma
TM>>>> n a g ement.smo.indexcollection_members.aspx
TM>>>>
TM>>>> which contain these
TM>>>>
TM>>>> http://technet.microsoft.com/zh-cn/library/microsoft.sqlserver.ma
TM>>>> n a g ement.smo.index.aspx
TM>>>>
TM>>>> While you could do this on oneline.. I think it is unreadable so
TM>>>> I would do this ( sorry I cant test im doing this in head :S )
TM>>>>
TM>>>> $Indexes = $smo.Databases["AdventureWorks"].Tables | Where-Object
TM>>>> {$_.Schema
TM>>>> -eq "Sales"}| Select-Object Indexes
TM>>>> foreach($Index in $Indexes)
TM>>>> {
TM>>>> $Index | %{$_.Name}
TM>>>> }
TM>>>> Brandon Shell
TM>>>> ---------------
TM>>>> Blog: http://www.bsonposh.com/
TM>>>> PSH Scripts Project: www.codeplex.com/psobject
TM>>>>> That was in my original post:
TM>>>>>
TM>>>>>>> $smo.Databases["AdventureWorks"].Tables | Where-Object
TM>>>>>>> {$_.Schema -eq "Sales"} | Select-Object Indexes
TM>>>>>>>
TM>>>>>>> ... I get the following output:
TM>>>>>>>
TM>>>>>>> Indexes
TM>>>>>>> -------
TM>>>>>>> {PK_ContactCreditCard_ContactID_CreditCardID}
TM>>>>>>> {IX_CountryRegionCurrency_CurrencyCode,
TM>>>>>>> PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode}
TM>>>>>>> {AK_CreditCard_CardNumber, PK_CreditCard_CreditCardID}
TM>>>>>>> {AK_Currency_Name, PK_Currency_CurrencyCode}
TM>>>>>>> {AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyC
TM>>>>>>> o
TM>>>>>>> d
TM>>>>>>> e
TM>>>>>>> ,
TM>>>>>>> PK_CurrencyRate_CurrencyRateID}
TM>>>>> ----------------------------------------------------
TM>>>>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>>>>> SQL Server MVP
TM>>>>> Toronto, ON Canada
TM>>>>> https://mvp.support.microsoft.com/profile/Tom.Moreau
TM>>>>> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in
TM>>>>> message
TM>>>>> news:29d4f64638818ca27dc0111014c@xxxxxx
TM>>>>> What do you get back when you do this.
TM>>>>> $smo.Databases["AdventureWorks"].Tables | Where-Object
TM>>>>> {$_.Schema
TM>>>>> -eq
TM>>>>> "Sales"}|
TM>>>>> Select-Object Indexes
TM>>>>> Brandon Shell
TM>>>>> ---------------
TM>>>>> Blog: http://www.bsonposh.com/
TM>>>>> PSH Scripts Project: www.codeplex.com/psobject
TM>>>>>> No, I get this back:
TM>>>>>>
TM>>>>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>>>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>>>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>>>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>>>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>>>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>>>>>
TM>>>>>> It really is supposed to be a collection of Index objects.
TM>>>>>>
TM>>>>>> ----------------------------------------------------
TM>>>>>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>>>>>> SQL Server MVP
TM>>>>>> Toronto, ON Canada
TM>>>>>> https://mvp.support.microsoft.com/profile/Tom.Moreau
TM>>>>>> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in
TM>>>>>> message
TM>>>>>> news:29d4f646387e8ca27da71057e80@xxxxxx
TM>>>>>> It seems to me it is just an array so this should work
TM>>>>>> $smo.Databases["AdventureWorks"].Tables | Where-Object
TM>>>>>> {$_.Schema
TM>>>>>> -eq
TM>>>>>> "Sales"}
TM>>>>>> | Select-Object Indexes | foreach {Write-Host $_}
TM>>>>>> Brandon Shell
TM>>>>>> ---------------
TM>>>>>> Blog: http://www.bsonposh.com/
TM>>>>>> PSH Scripts Project: www.codeplex.com/psobject
TM>>>>>>> I am running the following code:
TM>>>>>>>
TM>>>>>>> [void][reflection.assembly]::LoadWithPartialName("Microsoft.Sq
TM>>>>>>> l S e r v e r.Smo") $smo = new-object
TM>>>>>>> Microsoft.SqlServer.Management.Smo.Server ".\SQL2005"
TM>>>>>>>
TM>>>>>>> $smo.Databases["AdventureWorks"].Tables | Where-Object
TM>>>>>>> {$_.Schema -eq "Sales"} | Select-Object Indexes | foreach
TM>>>>>>> {Write-Host $_.name}
TM>>>>>>>
TM>>>>>>> The last statement writes an empty line for each table in the
TM>>>>>>> Tables collection. (I'm using Write-Host just for debugging.
TM>>>>>>> I actually want to manipulate each index.) When I run the
TM>>>>>>> following:
TM>>>>>>>
TM>>>>>>> $smo.Databases["AdventureWorks"].Tables | Where-Object
TM>>>>>>> {$_.Schema -eq "Sales"} | Select-Object Indexes
TM>>>>>>>
TM>>>>>>> ... I get the following output:
TM>>>>>>>
TM>>>>>>> Indexes
TM>>>>>>> -------
TM>>>>>>> {PK_ContactCreditCard_ContactID_CreditCardID}
TM>>>>>>> {IX_CountryRegionCurrency_CurrencyCode,
TM>>>>>>> PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode}
TM>>>>>>> {AK_CreditCard_CardNumber, PK_CreditCard_CreditCardID}
TM>>>>>>> {AK_Currency_Name, PK_Currency_CurrencyCode}
TM>>>>>>> {AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyC
TM>>>>>>> o
TM>>>>>>> d
TM>>>>>>> e
TM>>>>>>> ,
TM>>>>>>> PK_CurrencyRate_CurrencyRateID}
TM>>>>>>> ...
TM>>>>>>> How do I pick up the object within the foreach loop?
TM>>>>>>> ----------------------------------------------------
TM>>>>>>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>>>>>>> SQL Server MVP
TM>>>>>>> Toronto, ON Canada
TM>>>>>>> https://mvp.support.microsoft.com/profile/Tom.Moreau
 

My Computer

T

Tom Moreau

#14
Yikes! That's bizarre.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Shay Levi" <no@xxxxxx> wrote in message
news:8766a9441aac68ca283d5e88f108@xxxxxx

Also, select and foreach has different outputs:

PS > $smo.Databases["msdb"].Tables["msdbms"].indexes

(...)
Name : pk_MSdbms
SpaceUsed : 8
(...)



# foreach shows that there is a value for the spaceused
PS > $smo.Databases["msdb"].Tables["msdbms"].indexes | foreach
{$_.name;$_.spaceused}
pk_MSdbms
8

# select doesn't show it
PS > $smo.Databases["msdb"].Tables["msdbms"].indexes | select name,spaceused

Name
----
pk_MSdbms


The same with:

PS > $smo.Databases["msdb"].Tables["msdbms"].indexes | format-list
name,space*

Name : pk_MSdbms
SpaceUsed : 8


PS > $smo.Databases["msdb"].Tables["msdbms"].indexes | format-table
name,space*

Name
----
pk_MSdbms



Got to dig some more...


-----
Shay Levi
$cript Fanatic
http://scriptolog.blogspot.com

> Hi Thomas,
>
> For what its worth, I've been able to run this on my local db server:
>
> [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.
> Smo") $smo = new-object Microsoft.SqlServer.Management.Smo.Server
> ".\sqlexpress" $smo.Databases["test"].Tables["tbl1"].indexes | select
> name
>
> Name
> ----
> myIndex
> -----
> Shay Levi
> $cript Fanatic
> http://scriptolog.blogspot.com

>> Well, you got me to thinking and that's half the battle. I have lots
>> of Perl and VBScript and this PS stuff is new to me. It's fun,
>> though. The ability to keep pipelining stuff looks pretty kewl to
>> me.
>>
>> ----------------------------------------------------
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in message
>> news:29d4f64638b48ca27fe5ea42989@xxxxxx
>> I wish I could have been more help :S Glad you got it working :)
>> Brandon Shell
>> ---------------
>> Blog: http://www.bsonposh.com/
>> PSH Scripts Project: www.codeplex.com/psobject
TM>>> I got nothing back from that, too. I have made some progress,
TM>>> though. Here' what I have so far:
TM>>>
TM>>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM>>> -eq "Sales"} | foreach {$Table = $_.Name; Write-Host $Table;
TM>>> foreach ($Index in $_.Indexes) {Write-host "`t" $Index.Name "`t"
TM>>> $Index.SpaceUsed}}
TM>>>
TM>>> When I add other methods in the second foreach, I'm not seeing
TM>>> evidence that they were actually sent to SQL Server, but that may
TM>>> be more of a SMO issue than PowerShell.
TM>>>
TM>>> ----------------------------------------------------
TM>>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>>> SQL Server MVP
TM>>> Toronto, ON Canada
TM>>> https://mvp.support.microsoft.com/profile/Tom.Moreau
TM>>> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in message
TM>>> news:29d4f64638948ca27e45cab9041@xxxxxx
TM>>> That is because Indexes is an array of arrays
TM>>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema
TM>>> -eq
TM>>> "Sales"}|
TM>>> Select-Object Indexes | %{$_} | %{$_.name}
TM>>> Perhaps I can explain what I think is happening
TM>>> * Select-Object Indexes # this passes just the Collection of Index
TM>>> Collections
TM>>> down the pipe
TM>>> * | %{$_} # This takes each Index Collection and
TM>>> passes
TM>>> it down the pipe
TM>>> * | %{$_.name} # This should get each Index in the Index
TM>>> Collection
TM>>> and Ouput just the Name
TM>>> Brandon Shell
TM>>> ---------------
TM>>> Blog: http://www.bsonposh.com/
TM>>> PSH Scripts Project: www.codeplex.com/psobject
TM>>>> I get nothing back from this. I still don't get why foreach just
TM>>>> isn't getting the object.
TM>>>>
TM>>>> ----------------------------------------------------
TM>>>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>>>> SQL Server MVP
TM>>>> Toronto, ON Canada
TM>>>> https://mvp.support.microsoft.com/profile/Tom.Moreau
TM>>>> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in
TM>>>> message
TM>>>> news:29d4f646388c8ca27e0af10ce1d@xxxxxx
TM>>>> Ok.. I think the problem is that Indexes returns and array of
TM>>>> These
TM>>>> http://technet.microsoft.com/zh-cn/library/microsoft.sqlserver.ma
TM>>>> n a g ement.smo.indexcollection_members.aspx
TM>>>>
TM>>>> which contain these
TM>>>>
TM>>>> http://technet.microsoft.com/zh-cn/library/microsoft.sqlserver.ma
TM>>>> n a g ement.smo.index.aspx
TM>>>>
TM>>>> While you could do this on oneline.. I think it is unreadable so
TM>>>> I would do this ( sorry I cant test im doing this in head :S )
TM>>>>
TM>>>> $Indexes = $smo.Databases["AdventureWorks"].Tables | Where-Object
TM>>>> {$_.Schema
TM>>>> -eq "Sales"}| Select-Object Indexes
TM>>>> foreach($Index in $Indexes)
TM>>>> {
TM>>>> $Index | %{$_.Name}
TM>>>> }
TM>>>> Brandon Shell
TM>>>> ---------------
TM>>>> Blog: http://www.bsonposh.com/
TM>>>> PSH Scripts Project: www.codeplex.com/psobject
TM>>>>> That was in my original post:
TM>>>>>
TM>>>>>>> $smo.Databases["AdventureWorks"].Tables | Where-Object
TM>>>>>>> {$_.Schema -eq "Sales"} | Select-Object Indexes
TM>>>>>>>
TM>>>>>>> ... I get the following output:
TM>>>>>>>
TM>>>>>>> Indexes
TM>>>>>>> -------
TM>>>>>>> {PK_ContactCreditCard_ContactID_CreditCardID}
TM>>>>>>> {IX_CountryRegionCurrency_CurrencyCode,
TM>>>>>>> PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode}
TM>>>>>>> {AK_CreditCard_CardNumber, PK_CreditCard_CreditCardID}
TM>>>>>>> {AK_Currency_Name, PK_Currency_CurrencyCode}
TM>>>>>>> {AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyC
TM>>>>>>> o
TM>>>>>>> d
TM>>>>>>> e
TM>>>>>>> ,
TM>>>>>>> PK_CurrencyRate_CurrencyRateID}
TM>>>>> ----------------------------------------------------
TM>>>>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>>>>> SQL Server MVP
TM>>>>> Toronto, ON Canada
TM>>>>> https://mvp.support.microsoft.com/profile/Tom.Moreau
TM>>>>> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in
TM>>>>> message
TM>>>>> news:29d4f64638818ca27dc0111014c@xxxxxx
TM>>>>> What do you get back when you do this.
TM>>>>> $smo.Databases["AdventureWorks"].Tables | Where-Object
TM>>>>> {$_.Schema
TM>>>>> -eq
TM>>>>> "Sales"}|
TM>>>>> Select-Object Indexes
TM>>>>> Brandon Shell
TM>>>>> ---------------
TM>>>>> Blog: http://www.bsonposh.com/
TM>>>>> PSH Scripts Project: www.codeplex.com/psobject
TM>>>>>> No, I get this back:
TM>>>>>>
TM>>>>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>>>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>>>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>>>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>>>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>>>>> @{Indexes=Microsoft.SqlServer.Management.Smo.IndexCollection}
TM>>>>>>
TM>>>>>> It really is supposed to be a collection of Index objects.
TM>>>>>>
TM>>>>>> ----------------------------------------------------
TM>>>>>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>>>>>> SQL Server MVP
TM>>>>>> Toronto, ON Canada
TM>>>>>> https://mvp.support.microsoft.com/profile/Tom.Moreau
TM>>>>>> "Brandon Shell [MVP]" <a_bshell.mask@xxxxxx> wrote in
TM>>>>>> message
TM>>>>>> news:29d4f646387e8ca27da71057e80@xxxxxx
TM>>>>>> It seems to me it is just an array so this should work
TM>>>>>> $smo.Databases["AdventureWorks"].Tables | Where-Object
TM>>>>>> {$_.Schema
TM>>>>>> -eq
TM>>>>>> "Sales"}
TM>>>>>> | Select-Object Indexes | foreach {Write-Host $_}
TM>>>>>> Brandon Shell
TM>>>>>> ---------------
TM>>>>>> Blog: http://www.bsonposh.com/
TM>>>>>> PSH Scripts Project: www.codeplex.com/psobject
TM>>>>>>> I am running the following code:
TM>>>>>>>
TM>>>>>>> [void][reflection.assembly]::LoadWithPartialName("Microsoft.Sq
TM>>>>>>> l S e r v e r.Smo") $smo = new-object
TM>>>>>>> Microsoft.SqlServer.Management.Smo.Server ".\SQL2005"
TM>>>>>>>
TM>>>>>>> $smo.Databases["AdventureWorks"].Tables | Where-Object
TM>>>>>>> {$_.Schema -eq "Sales"} | Select-Object Indexes | foreach
TM>>>>>>> {Write-Host $_.name}
TM>>>>>>>
TM>>>>>>> The last statement writes an empty line for each table in the
TM>>>>>>> Tables collection. (I'm using Write-Host just for debugging.
TM>>>>>>> I actually want to manipulate each index.) When I run the
TM>>>>>>> following:
TM>>>>>>>
TM>>>>>>> $smo.Databases["AdventureWorks"].Tables | Where-Object
TM>>>>>>> {$_.Schema -eq "Sales"} | Select-Object Indexes
TM>>>>>>>
TM>>>>>>> ... I get the following output:
TM>>>>>>>
TM>>>>>>> Indexes
TM>>>>>>> -------
TM>>>>>>> {PK_ContactCreditCard_ContactID_CreditCardID}
TM>>>>>>> {IX_CountryRegionCurrency_CurrencyCode,
TM>>>>>>> PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode}
TM>>>>>>> {AK_CreditCard_CardNumber, PK_CreditCard_CreditCardID}
TM>>>>>>> {AK_Currency_Name, PK_Currency_CurrencyCode}
TM>>>>>>> {AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyC
TM>>>>>>> o
TM>>>>>>> d
TM>>>>>>> e
TM>>>>>>> ,
TM>>>>>>> PK_CurrencyRate_CurrencyRateID}
TM>>>>>>> ...
TM>>>>>>> How do I pick up the object within the foreach loop?
TM>>>>>>> ----------------------------------------------------
TM>>>>>>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
TM>>>>>>> SQL Server MVP
TM>>>>>>> Toronto, ON Canada
TM>>>>>>> https://mvp.support.microsoft.com/profile/Tom.Moreau
 

My Computer

J

Jeffrey Snover[MSFT]

#15
I'm not sure I follow the problem but I think this might do what you want.
Select has a parameter -EXPAND which unfolds collections.

$smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema -eq
"Sales"} | Select-Object -EXPAND Indexes | foreach {Write-Host $_.name}

--
Jeffrey P. Snover[MSFT]
Partner Architect, Windows Server
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom Moreau" <tom@xxxxxx> wrote in message
news:%237ahqweWIHA.4532@xxxxxx

>I am running the following code:
>
>
> [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
> $smo = new-object Microsoft.SqlServer.Management.Smo.Server ".\SQL2005"
>
> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema -eq
> "Sales"} | Select-Object Indexes | foreach {Write-Host $_.name}
>
> The last statement writes an empty line for each table in the Tables
> collection. (I'm using Write-Host just for debugging. I actually want to
> manipulate each index.) When I run the following:
>
> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema -eq
> "Sales"} | Select-Object Indexes
>
> ... I get the following output:
>
> Indexes
> -------
> {PK_ContactCreditCard_ContactID_CreditCardID}
> {IX_CountryRegionCurrency_CurrencyCode,
> PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode}
> {AK_CreditCard_CardNumber, PK_CreditCard_CreditCardID}
> {AK_Currency_Name, PK_Currency_CurrencyCode}
> {AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode,
> PK_CurrencyRate_CurrencyRateID}
> ...
>
>
> How do I pick up the object within the foreach loop?
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
>
 

My Computer

S

Steven Hystad

#16
You are miss interpreting what Select-Object does.

The command Select-Object Indexes actually creates a new object with a
property named Indexes that is the same as the current value of the Indexes
property of its input object. That is why you are getting a table with a
single column header of Indexes.
In this context this functionality is not particularly useful. To do what I
believe you want to do I would use the following command:

$smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema -eq
"Sales"} | foreach {$_.Indexes} | foreach {Write-Host $_.name}

"Tom Moreau" <tom@xxxxxx> wrote in message
news:%237ahqweWIHA.4532@xxxxxx

>I am running the following code:
>
>
> [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
> $smo = new-object Microsoft.SqlServer.Management.Smo.Server ".\SQL2005"
>
> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema -eq
> "Sales"} | Select-Object Indexes | foreach {Write-Host $_.name}
>
> The last statement writes an empty line for each table in the Tables
> collection. (I'm using Write-Host just for debugging. I actually want to
> manipulate each index.) When I run the following:
>
> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema -eq
> "Sales"} | Select-Object Indexes
>
> ... I get the following output:
>
> Indexes
> -------
> {PK_ContactCreditCard_ContactID_CreditCardID}
> {IX_CountryRegionCurrency_CurrencyCode,
> PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode}
> {AK_CreditCard_CardNumber, PK_CreditCard_CreditCardID}
> {AK_Currency_Name, PK_Currency_CurrencyCode}
> {AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode,
> PK_CurrencyRate_CurrencyRateID}
> ...
>
>
> How do I pick up the object within the foreach loop?
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
>
 

My Computer

T

Tom Moreau

#17
That works. Thanx!

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada

"Jeffrey Snover[MSFT]" <jsnover@xxxxxx> wrote in message
news:8507F1A9-1A37-4AF6-AB62-0D02C4E25519@xxxxxx
I'm not sure I follow the problem but I think this might do what you want.
Select has a parameter -EXPAND which unfolds collections.

$smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema -eq
"Sales"} | Select-Object -EXPAND Indexes | foreach {Write-Host $_.name}

--
Jeffrey P. Snover[MSFT]
Partner Architect, Windows Server
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom Moreau" <tom@xxxxxx> wrote in message
news:%237ahqweWIHA.4532@xxxxxx

>I am running the following code:
>
>
> [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
> $smo = new-object Microsoft.SqlServer.Management.Smo.Server ".\SQL2005"
>
> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema -eq
> "Sales"} | Select-Object Indexes | foreach {Write-Host $_.name}
>
> The last statement writes an empty line for each table in the Tables
> collection. (I'm using Write-Host just for debugging. I actually want to
> manipulate each index.) When I run the following:
>
> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema -eq
> "Sales"} | Select-Object Indexes
>
> ... I get the following output:
>
> Indexes
> -------
> {PK_ContactCreditCard_ContactID_CreditCardID}
> {IX_CountryRegionCurrency_CurrencyCode,
> PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode}
> {AK_CreditCard_CardNumber, PK_CreditCard_CreditCardID}
> {AK_Currency_Name, PK_Currency_CurrencyCode}
> {AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode,
> PK_CurrencyRate_CurrencyRateID}
> ...
>
>
> How do I pick up the object within the foreach loop?
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
>
 

My Computer

T

Tom Moreau

#18
OK, I've played around with this a bit. I'm trying to rebuild each index.
Here's the command line:

$smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema -eq
"Sales"} | foreach {$_.Indexes} | foreach {$_.Rebuild}

It doesn't rebuild the index. Rather, I get a bunch of the following being
printed to the console:

MemberType : Method
OverloadDefinitions : {System.Void Rebuild()}
TypeNameOfValue : System.Management.Automation.PSMethod
Value : System.Void Rebuild()
Name : Rebuild
IsInstance : True


--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada

"Steven Hystad" <steven.hystad@xxxxxx> wrote in message
news:OC8pOX1WIHA.4140@xxxxxx
You are miss interpreting what Select-Object does.

The command Select-Object Indexes actually creates a new object with a
property named Indexes that is the same as the current value of the Indexes
property of its input object. That is why you are getting a table with a
single column header of Indexes.
In this context this functionality is not particularly useful. To do what I
believe you want to do I would use the following command:

$smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema -eq
"Sales"} | foreach {$_.Indexes} | foreach {Write-Host $_.name}

"Tom Moreau" <tom@xxxxxx> wrote in message
news:%237ahqweWIHA.4532@xxxxxx

>I am running the following code:
>
>
> [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
> $smo = new-object Microsoft.SqlServer.Management.Smo.Server ".\SQL2005"
>
> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema -eq
> "Sales"} | Select-Object Indexes | foreach {Write-Host $_.name}
>
> The last statement writes an empty line for each table in the Tables
> collection. (I'm using Write-Host just for debugging. I actually want to
> manipulate each index.) When I run the following:
>
> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema -eq
> "Sales"} | Select-Object Indexes
>
> ... I get the following output:
>
> Indexes
> -------
> {PK_ContactCreditCard_ContactID_CreditCardID}
> {IX_CountryRegionCurrency_CurrencyCode,
> PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode}
> {AK_CreditCard_CardNumber, PK_CreditCard_CreditCardID}
> {AK_Currency_Name, PK_Currency_CurrencyCode}
> {AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode,
> PK_CurrencyRate_CurrencyRateID}
> ...
>
>
> How do I pick up the object within the foreach loop?
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
>
 

My Computer

B

Brandon Shell [MVP]

#19
rebuild needs ()

all method calls require ()

$smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema -eq
"Sales"} | foreach {$_.Indexes} | foreach {$_.Rebuild()}

"Tom Moreau" <tom@xxxxxx> wrote in message
news:O%23n525tXIHA.4808@xxxxxx

> OK, I've played around with this a bit. I'm trying to rebuild each index.
> Here's the command line:
>
> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema -eq
> "Sales"} | foreach {$_.Indexes} | foreach {$_.Rebuild}
>
> It doesn't rebuild the index. Rather, I get a bunch of the following
> being
> printed to the console:
>
> MemberType : Method
> OverloadDefinitions : {System.Void Rebuild()}
> TypeNameOfValue : System.Management.Automation.PSMethod
> Value : System.Void Rebuild()
> Name : Rebuild
> IsInstance : True
>
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
>
> "Steven Hystad" <steven.hystad@xxxxxx> wrote in message
> news:OC8pOX1WIHA.4140@xxxxxx
> You are miss interpreting what Select-Object does.
>
> The command Select-Object Indexes actually creates a new object with a
> property named Indexes that is the same as the current value of the
> Indexes
> property of its input object. That is why you are getting a table with a
> single column header of Indexes.
> In this context this functionality is not particularly useful. To do what
> I
> believe you want to do I would use the following command:
>
> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema -eq
> "Sales"} | foreach {$_.Indexes} | foreach {Write-Host $_.name}
>
> "Tom Moreau" <tom@xxxxxx> wrote in message
> news:%237ahqweWIHA.4532@xxxxxx

>>I am running the following code:
>>
>>
>> [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
>> $smo = new-object Microsoft.SqlServer.Management.Smo.Server ".\SQL2005"
>>
>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema -eq
>> "Sales"} | Select-Object Indexes | foreach {Write-Host $_.name}
>>
>> The last statement writes an empty line for each table in the Tables
>> collection. (I'm using Write-Host just for debugging. I actually want
>> to
>> manipulate each index.) When I run the following:
>>
>> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema -eq
>> "Sales"} | Select-Object Indexes
>>
>> ... I get the following output:
>>
>> Indexes
>> -------
>> {PK_ContactCreditCard_ContactID_CreditCardID}
>> {IX_CountryRegionCurrency_CurrencyCode,
>> PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode}
>> {AK_CreditCard_CardNumber, PK_CreditCard_CreditCardID}
>> {AK_Currency_Name, PK_Currency_CurrencyCode}
>> {AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode,
>> PK_CurrencyRate_CurrencyRateID}
>> ...
>>
>>
>> How do I pick up the object within the foreach loop?
>> --
>> Tom
>>
>> ----------------------------------------------------
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>>
>
>
 

My Computer

O

Oisin (x0n) Grehan [MVP]

#20
On Jan 24, 6:10 pm, "Tom Moreau" <t...@xxxxxx> wrote:

> OK, I've played around with this a bit.  I'm trying to rebuild each index.
> Here's the command line:
>
> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema -eq
> "Sales"} | foreach {$_.Indexes} | foreach {$_.Rebuild}
>
> It doesn't rebuild the index.  Rather, I get a bunch of the following being
> printed to the console:
>
> MemberType          : Method
> OverloadDefinitions : {System.Void Rebuild()}
> TypeNameOfValue     : System.Management.Automation.PSMethod
> Value               : System.Void Rebuild()
> Name                : Rebuild
> IsInstance          : True
>
> --
>     Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON   Canada
>
> "Steven Hystad" <steven.hys...@xxxxxx> wrote in message
>
> news:OC8pOX1WIHA.4140@xxxxxx
> You are miss interpreting what  Select-Object does.
>
> The command Select-Object Indexes actually creates a new object with a
> property named Indexes that is the same as the current value of the Indexes
> property of its input object. That is why you are getting a table with a
> single column header of Indexes.
> In this context this functionality is not particularly useful. To do what I
> believe you want to do I would use the following command:
>
> $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema -eq
> "Sales"} | foreach {$_.Indexes} | foreach {Write-Host $_.name}
>
> "Tom Moreau" <t...@xxxxxx> wrote in message
>
> news:%237ahqweWIHA.4532@xxxxxx
>
>
>

> >I am running the following code:
>

> > [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
> > $smo = new-object Microsoft.SqlServer.Management.Smo.Server ".\SQL2005"
>

> > $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema -eq
> > "Sales"} | Select-Object Indexes | foreach {Write-Host $_.name}
>

> > The last statement writes an empty line for each table in the Tables
> > collection.  (I'm using Write-Host just for debugging.  I actually want to
> > manipulate each index.)  When I run the following:
>

> > $smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema -eq
> > "Sales"} | Select-Object Indexes
>

> > ... I get the following output:
>

> > Indexes
> > -------
> > {PK_ContactCreditCard_ContactID_CreditCardID}
> > {IX_CountryRegionCurrency_CurrencyCode,
> > PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode}
> > {AK_CreditCard_CardNumber, PK_CreditCard_CreditCardID}
> > {AK_Currency_Name, PK_Currency_CurrencyCode}
> > {AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode,
> > PK_CurrencyRate_CurrencyRateID}
> > ...
>

> > How do I pick up the object within the foreach loop?
> > --
> >   Tom
>

> > ----------------------------------------------------
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON   Canada
> >https://mvp.support.microsoft.com/profile/Tom.Moreau- Hide quoted text -
>
> - Show quoted text -
Hi Tom,

Rebuild is a method (an Action to be performed on the object), so you
need parentheses on Rebuild:

$smo.Databases["AdventureWorks"].Tables | Where-Object {$_.Schema -eq
"Sales"} | foreach {$_.Indexes} | foreach {$_.Rebuild() }

Hope this helps,

- Oisin
 

My Computer

Users Who Are Viewing This Thread (Users: 1, Guests: 0)