This came up on the Powershell newsgroup, thought I’d post it here as well.

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:

   1: $SchemaCol = @{

   2:     Name="Schema"

   3:     Expression={$_.Parent.Schema}

   4: }

   5: $TableCol = @{Name="Table"; Expression={$_.Parent.Name}}

   6: $NullableCol = @{ 

   7:     Name = "Nullable"

   8:     Expr = {if($_.Nullable -eq $TRUE){"YES"}else{"NO"}}}

   9: } 

Then you execute it like so:

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