locked
dump columns from query to singe row RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

     

     This script creates the file I want,but I would like the output to be in a single row with just column names.

     Thanks.

    Param(
    $server='(local)',
    $dbname='copydb1',
    $tablename='analytics_stg',
    $schema='dbo'
)

$sqlConnection="Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=$dbname;Data Source=$server"

Try{
    $conn = New-Object System.Data.SqlClient.SqlConnection($sqlConnection)
    [void]$conn.open()
    $adapter=New-Object Data.Sqlclient.SqlDataAdapter("select column_name from information_schema.COLUMNS where table_name = '$tablename'", $conn)
    $ds=new-object System.Data.DataTable
    [void]$adapter.Fill($ds)
    $ds | Export-Csv -Path 'c:\analyticshdr.csv' -NoTypeInformation 
}

Catch{
    Write-Host "$_" -ForegroundColor red
}

Finally{
    $conn.Close()
}

    Wednesday, March 13, 2019 2:35 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    You need to use a data table and not a dataset.  The adapter method is for different things like binding to a form.

    Try {
    $conn = New-Object System.Data.SqlClient.SqlConnection($sqlConnection)
    [void]$conn.open()
    $cmd = $conn.CreateCommand()
    $cmd.CommandText = "select column_name from information_schema.COLUMNS where table_name = '$tablename'"
    $rdr = $cmd.ExecuteReader()
        $dt = new-object System.Data.DataTable
    [void]$dt.Load($rdr)
    $dt | Export-Csv -Path 'c:\analyticshdr.csv' -NoTypeInformation
}

    Also the output will be rows containing column definitions.  If you just want a list of names then just select the "Name" as noted above then join the array.

    ($dt | Select -expand column_name ) -join ','

    The last line would then become:

    ($dt | Select -expand column_name ) -join ',' | Out-File 'c:\analyticshdr.csv'

    If column names have space you will have to add quotes.

    Another method to do this quickly is to select a single record from the target table and then clear the resulting table.  When exported the file will only have the column headers and no data.

    \_(ツ)_/








    • Edited by jrv Wednesday, March 13, 2019 3:48 PM
    • Marked as answer by hart60 Wednesday, March 13, 2019 3:56 PM
    Wednesday, March 13, 2019 3:39 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    and Select-Object -Property names does not work?

    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''

    Wednesday, March 13, 2019 3:06 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    You need to use a data table and not a dataset.  The adapter method is for different things like binding to a form.

    Try {
    $conn = New-Object System.Data.SqlClient.SqlConnection($sqlConnection)
    [void]$conn.open()
    $cmd = $conn.CreateCommand()
    $cmd.CommandText = "select column_name from information_schema.COLUMNS where table_name = '$tablename'"
    $rdr = $cmd.ExecuteReader()
        $dt = new-object System.Data.DataTable
    [void]$dt.Load($rdr)
    $dt | Export-Csv -Path 'c:\analyticshdr.csv' -NoTypeInformation
}

    Also the output will be rows containing column definitions.  If you just want a list of names then just select the "Name" as noted above then join the array.

    ($dt | Select -expand column_name ) -join ','

    The last line would then become:

    ($dt | Select -expand column_name ) -join ',' | Out-File 'c:\analyticshdr.csv'

    If column names have space you will have to add quotes.

    Another method to do this quickly is to select a single record from the target table and then clear the resulting table.  When exported the file will only have the column headers and no data.

    \_(ツ)_/








    • Edited by jrv Wednesday, March 13, 2019 3:48 PM
    • Marked as answer by hart60 Wednesday, March 13, 2019 3:56 PM
    Wednesday, March 13, 2019 3:39 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

     Still gave me data in column format not a single row of the column names.

     Thanks.

    Wednesday, March 13, 2019 3:39 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

     I got this error:

     Cannot find an overload for "Load" and the argument count: "1".

     Thanks.

    Wednesday, March 13, 2019 3:47 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    This will get you an export of all column names as a header in the file and will place quotes on the columns:

    ...
    $cmd.CommandText = "select TOP 1 * from $tablename"
...
[void]$dt.Load($dt)
$dt.Clear()
$dt | Export-Csv -Path 'c:\analyticshdr.csv' -NoTypeInformation

    \_(ツ)_/




    • Edited by jrv Wednesday, March 13, 2019 3:52 PM
    Wednesday, March 13, 2019 3:49 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

     I got this error:

     Cannot find an overload for "Load" and the argument count: "1".

     Thanks.

    Recopy the code - I fixed the error but you grabbed the old copy before I could finish.

    \_(ツ)_/

    Wednesday, March 13, 2019 3:53 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

     Yep.. that was it..

    Many Thanks!!!!!

    Wednesday, March 13, 2019 3:57 PM