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

1 year, 5 months ago.

  • Author
  • #74383

    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"
    $SQLCon = New-Object System.Data.SqlClient.SqlConnection("Data Source=$DBServer; `
    		  Initial Catalog=$DBName;Integrated Security=False;User ID=$Uid;Password=$Pwd")
            $SQL = $SQLCon.CreateCommand()
    $query = 
                declare @Result varchar(max)='
                select @Result=@Result+''+ColumnName+'
                        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”
    Write-Host ($table | Format-Table | Out-String)

    Thank you

  • #74386

    Points: 200
    Helping Hand
    Rank: 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

      Points: 0
      Rank: Member

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

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