locked
dump columns from query to singe row RRS feed

  • Question

  •  

     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

  • 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

  • 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
  • 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
  •  Still gave me data in column format not a single row of the column names.

     Thanks.

    Wednesday, March 13, 2019 3:39 PM
  •  I got this error:

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

     Thanks.

    Wednesday, March 13, 2019 3:47 PM
  • 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
  •  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
  •  Yep.. that was it..

    Many Thanks!!!!!

    Wednesday, March 13, 2019 3:57 PM