SQL table header names export

This topic contains 2 replies, has 2 voices, and was last updated by  taotao07 3 months, 2 weeks ago.

  • Author
    Posts
  • #74383

    taotao07
    Participant

    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

  • #74386

    Fredrik Kacsmarck
    Participant

    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.

    • #74480

      taotao07
      Participant

      1. yes i do get a correct result in the management studio
      2. I'm getting just the header names.
      THanks!

You must be logged in to reply to this topic.