SQL table header names export

Welcome Forums General PowerShell Q&A SQL table header names export

This topic contains 2 replies, has 2 voices, and was last updated by

 
Participant
1 year, 8 months ago.

  • Author
    Posts
  • #74383

    Participant
    Points: 0
    Rank: Member

    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

    Participant
    Points: 364
    Helping Hand
    Rank: Contributor

    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

      Participant
      Points: 0
      Rank: Member

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

The topic ‘SQL table header names export’ is closed to new replies.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort