SQL table header names export

PowerShell Help
1

Hi all, I’m trying to get output of this little script into a variable in powershell, however, I can’t figure out how to do it, I just need to query column names and add them to an array, that’s it, no need for any other data… looks pretty simple, eh?

 
$DBServer = "server\instance"
$DBName = "dbname"
$Uid="sa"
$Pwd="Password"
$SQLCon = New-Object System.Data.SqlClient.SqlConnection("Data Source=$DBServer; `
		  Initial Catalog=$DBName;Integrated Security=False;User ID=$Uid;Password=$Pwd")
	$SQLCon.open()
        $SQL = $SQLCon.CreateCommand()

$query = 
@"
            declare @Result varchar(max)='
            '
            select @Result=@Result+''+ColumnName+'
            '
            from
            (
                select
                    replace(col.name, ' ', '_') ColumnName,
                    column_id ColumnId
                from sys.columns col
                    join sys.types typ on
                        col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
                where object_id = object_id('dbo.sometable')
            ) t
            order by ColumnName;
            print @Result
"@
$SQL.CommandText = $query
$Result1 = $SQL.ExecuteReader()
$table = new-object “System.Data.DataTable”
$table.Load($Result1)
Write-Host ($table | Format-Table | Out-String)
$SQLCon.Close()

Thank you

2

Don’t have a SQL server to test right now but I guess the first questions are.

  1. Does the query produce the correct output in e.g. SQL Management Studio?
  2. What is the result you’re getting.
3
  1. yes i do get a correct result in the management studio
  2. I’m getting just the header names.
    THanks!