I'm having an issue with select-object. it seems like certain properties are
not being populated from the object:


[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") |
out-null

$server = new-object Microsoft.SqlServer.Management.Smo.Server($serverName)
$database = $server.Databases | where -filter {$_.Name -eq "$databaseName"}

$database.Tables |% { $_.Columns | select Parent.Schema, Name, DataType,
DataType.MaximumLength }


With this I get only the values for name and datatype populated. The other
properties are not being displayed. I think it might have something to do
with navigating the parent and child objects as properties.

If I use this line:



$database.Tables |% { $_.Columns | select * }

it seems to return the values

Any Ideas?

Select-Object can't take 'Parent.Schema' as a property (there is no property
with such name
on the Columns member). However, you can get it with select-object when you
specify it as a 'calculated property' and
then use it as any other 'column name':

$ParentSchema = @{name="ParentSchema";expression={$_.Parent.Schema}}
$database.Tables | foreach { $_.Columns | select $ParentSchema, Name...


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

> I'm having an issue with select-object. it seems like certain
> properties are not being populated from the object:
>
> [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
> | out-null
>
> $server = new-object
> Microsoft.SqlServer.Management.Smo.Server($serverName) $database =
> $server.Databases | where -filter {$_.Name -eq "$databaseName"}
>
> $database.Tables |% { $_.Columns | select Parent.Schema, Name,
> DataType, DataType.MaximumLength }
>
> With this I get only the values for name and datatype populated. The
> other properties are not being displayed. I think it might have
> something to do with navigating the parent and child objects as
> properties.
>
> If I use this line:
>
> $database.Tables |% { $_.Columns | select * }
>
> it seems to return the values
>
> Any Ideas?
>

The same applies for DataType.MaximumLength

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

> Select-Object can't take 'Parent.Schema' as a property (there is no
> property
> with such name
> on the Columns member). However, you can get it with select-object
> when you
> specify it as a 'calculated property' and
> then use it as any other 'column name':
> $ParentSchema = @{name="ParentSchema";expression={$_.Parent.Schema}}
> $database.Tables | foreach { $_.Columns | select $ParentSchema,
> Name...
>
> -----
> Shay Levi
> $cript Fanatic
> http://scriptolog.blogspot.com

>> I'm having an issue with select-object. it seems like certain
>> properties are not being populated from the object:
>>
>> [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
>> | out-null
>>
>> $server = new-object
>> Microsoft.SqlServer.Management.Smo.Server($serverName) $database =
>> $server.Databases | where -filter {$_.Name -eq "$databaseName"}
>>
>> $database.Tables |% { $_.Columns | select Parent.Schema, Name,
>> DataType, DataType.MaximumLength }
>>
>> With this I get only the values for name and datatype populated. The
>> other properties are not being displayed. I think it might have
>> something to do with navigating the parent and child objects as
>> properties.
>>
>> If I use this line:
>>
>> $database.Tables |% { $_.Columns | select * }
>>
>> it seems to return the values
>>
>> Any Ideas?
>>

ok so I ended up with this:

$database.Tables |% { $_.Columns | select @{Name="Schema";
Expression={$_.Parent.Schema}}, @{Name="Table"; Expression={$_.Parent.Name}},
Name, DataType, @{Name="MaxLength"; Expression={$_.DataType.MaximumLength}},
@{Name="Nullable"; Expression={if($_.Nullable -eq $True){"YES"}Else{"NO"}}},
@{Name="Precision";
Expression={$_.DataType.NumericPrecision}},@{Name="Scale";
Expression={$_.DataType.NumericScale}} } | Export-Csv -noTypeInformation
$exportFileName


now I have an export my db schema to a csv file

Thanks for the quick response

"Shay Levi" wrote:

> The same applies for DataType.MaximumLength
>
> -----
> Shay Levi
> $cript Fanatic
> http://scriptolog.blogspot.com
>

> > Select-Object can't take 'Parent.Schema' as a property (there is no
> > property
> > with such name
> > on the Columns member). However, you can get it with select-object
> > when you
> > specify it as a 'calculated property' and
> > then use it as any other 'column name':
> > $ParentSchema = @{name="ParentSchema";expression={$_.Parent.Schema}}
> > $database.Tables | foreach { $_.Columns | select $ParentSchema,
> > Name...
> >
> > -----
> > Shay Levi
> > $cript Fanatic
> > http://scriptolog.blogspot.com

> >> I'm having an issue with select-object. it seems like certain
> >> properties are not being populated from the object:
> >>
> >> [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
> >> | out-null
> >>
> >> $server = new-object
> >> Microsoft.SqlServer.Management.Smo.Server($serverName) $database =
> >> $server.Databases | where -filter {$_.Name -eq "$databaseName"}
> >>
> >> $database.Tables |% { $_.Columns | select Parent.Schema, Name,
> >> DataType, DataType.MaximumLength }
> >>
> >> With this I get only the values for name and datatype populated. The
> >> other properties are not being displayed. I think it might have
> >> something to do with navigating the parent and child objects as
> >> properties.
> >>
> >> If I use this line:
> >>
> >> $database.Tables |% { $_.Columns | select * }
> >>
> >> it seems to return the values
> >>
> >> Any Ideas?
> >>

>
>
>

Great. btw.. you don't need to create 'calculated property for 'Nullable'
, you can specifiy it as is to select-object since it's
a direct member of the Columns member.


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

> ok so I ended up with this:
>
> $database.Tables |% { $_.Columns | select @{Name="Schema";
> Expression={$_.Parent.Schema}}, @{Name="Table";
> Expression={$_.Parent.Name}}, Name, DataType, @{Name="MaxLength";
> Expression={$_.DataType.MaximumLength}}, @{Name="Nullable";
> Expression={if($_.Nullable -eq $True){"YES"}Else{"NO"}}},
> @{Name="Precision";
> Expression={$_.DataType.NumericPrecision}},@{Name="Scale";
> Expression={$_.DataType.NumericScale}} } | Export-Csv
> -noTypeInformation $exportFileName
>
> now I have an export my db schema to a csv file
>
> Thanks for the quick response
>
> "Shay Levi" wrote:
>

>> The same applies for DataType.MaximumLength
>>
>> -----
>> Shay Levi
>> $cript Fanatic
>> http://scriptolog.blogspot.com

>>> Select-Object can't take 'Parent.Schema' as a property (there is no
>>> property
>>> with such name
>>> on the Columns member). However, you can get it with select-object
>>> when you
>>> specify it as a 'calculated property' and
>>> then use it as any other 'column name':
>>> $ParentSchema = @{name="ParentSchema";expression={$_.Parent.Schema}}
>>> $database.Tables | foreach { $_.Columns | select $ParentSchema,
>>> Name...
>>> -----
>>> Shay Levi
>>> $cript Fanatic
>>> http://scriptolog.blogspot.com
>>>> I'm having an issue with select-object. it seems like certain
>>>> properties are not being populated from the object:
>>>>
>>>> [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo
>>>> ") | out-null
>>>>
>>>> $server = new-object
>>>> Microsoft.SqlServer.Management.Smo.Server($serverName) $database =
>>>> $server.Databases | where -filter {$_.Name -eq "$databaseName"}
>>>>
>>>> $database.Tables |% { $_.Columns | select Parent.Schema, Name,
>>>> DataType, DataType.MaximumLength }
>>>>
>>>> With this I get only the values for name and datatype populated.
>>>> The other properties are not being displayed. I think it might have
>>>> something to do with navigating the parent and child objects as
>>>> properties.
>>>>
>>>> If I use this line:
>>>>
>>>> $database.Tables |% { $_.Columns | select * }
>>>>
>>>> it seems to return the values
>>>>
>>>> Any Ideas?
>>>>

Jason wrote:

> ok so I ended up with this:
>
> $database.Tables |% { $_.Columns | select @{Name="Schema";
> Expression={$_.Parent.Schema}}, @{Name="Table"; Expression={$_.Parent.Name}},
> Name, DataType, @{Name="MaxLength"; Expression={$_.DataType.MaximumLength}},
> @{Name="Nullable"; Expression={if($_.Nullable -eq $True){"YES"}Else{"NO"}}},
> @{Name="Precision";
> Expression={$_.DataType.NumericPrecision}},@{Name="Scale";
> Expression={$_.DataType.NumericScale}} } | Export-Csv -noTypeInformation
> $exportFileName

....which all will agree is a mess.

I find that calc'd properties inevitably grow during creation so I've taken to
separating out the scriptblock (or the whole hashtable) out to a separate
command. Here's how:

$NullableSb = {if($_.Nullable -eq $True){"YES"}Else{"NO"}}}

then you'd have something like this:

$database.tables | % { $_.columns | select @{Name=Nullable; Expr=$NullableSb} }

Or you can back out one step further:

$SchemaCol = @{
Name="Schema"
Expression={$_.Parent.Schema}
}
$TableCol = @{Name="Table"; Expression={$_.Parent.Name}}
$NullableCol = @{
Name = "Nullable"
Expr = {if($_.Nullable -eq $True){"YES"}Else{"NO"}}}
}

Then you execute it like so:

$database.tables | % { $_.columns | select $SchemaCol,$TableCol,$NullableCol }

--

Hal Rottenberg
Blog: http://halr9000.com
Webmaster, Psi (http://psi-im.org)
Co-host, PowerScripting Podcast (http://powerscripting.net)