Show null values ​​in result table when executing SQL query

Question

I have a script that executes SQL files and prints the result in the console (for now). The problem is that I need to distinguish between NULL values ​​and empty rows that are returned in the result table.

This is what the query returns in Studio Management:

You can see that it contains strings, empty strings and NULL values.

This is what the query returns in PowerShell:

There is no difference between NULL and empty.

In addition, the last few columns are cut out and only the first 10 are printed.

How can i fix this?

Here is my code:

$ConnectionString = "Data Source=...;Initial Catalog=...;User Id=..;Password=.."
$FolderToSQLFiles = "C:\SQLFilesTestFolder";

#function that executes sql queries and return result tables
function GetSQLresults {
    Param(
        [Parameter(Mandatory=$True, ValueFromPipelineByPropertyName=$True, Position=0)] $SQLqueryText, # sql query text returned from file
    )

    $objConnection = New-Object System.Data.SqlClient.SqlConnection;
    $objConnection.ConnectionString = $ConnectionString

    $objConnection.Open();

    $ObjCmd = New-Object System.Data.SqlClient.SqlCommand;
    $ObjCmd.CommandText = $SQLqueryText;
    $ObjCmd.Connection = $objConnection;
    $ObjCmd.CommandTimeout = 0;

    $objAdapter = New-Object System.Data.SqlClient.SqlDataAdapter;
    $objAdapter.SelectCommand = $ObjCmd;
    $objDataSet = New-Object System.Data.DataSet;

    $objAdapter.Fill($objDataSet);

    $ResultSets = @(); #adding all result tables to array

    for ($i=0; $i -lt $objDataSet.Tables.Count; $i++) {
        $tmpResultTable = $objDataSet.Tables[$i] | Format-Table | Out-String;
        $ResultSets += $tmpResultTable;
    }

    return $ResultSets 

    $query = $null;

    $objDataSet = $null;

    $objConnection.Close();
    $objConnection = $null;
}

Get-ChildItem $FolderToSQLFiles -Filter *.sql | Foreach-Object {
    $tmpSQLfilePath = $_.FullName #getting the sql file full path
    $tmpSQLfileContent = (Get-Content $tmpSQLfilePath) -join "`n"  #getting the content of the sql

    GetSQLresults -SQLqueryText $tmpSQLfileContent  #passing the sql query to the executing funciton
}

Answer 1

(Col006_Description) SELECT, :

SELECT ..., CASE WHEN Col006 IS NULL THEN '(null)' WHEN Col006 = '' THEN '(empty)' ELSE '' END AS Col006_Description, ...

Answer 2

PowerShell . /.

- :

GetSQLresults ... |
    Select-Object -Property *,@{n='Col006',e={
        if ($_.Col006 -is [DBNull]) {'NULL'} else {$_.Col006}
    }} -Exclude Col006