6

I want to remove column header from SQL Server query output. I did the search but not found any solution. I have a query eg. 

select cc.DepartmentID , cc.Name  from HumanResources.Department cc

When I run this query I am getting output like this.

ID  Name
12  Document Control
1   Engineering
16  Executive
14  Facilities and Maintenance
10  Finance
9   Human Resources

I want to remove ID and Name (Column Header) from the output in SQL Server.

I will run this query by script to generate csv file.

Edit:

When i run the query by script i got the csv file as output and it look like this.

#TYPE System.Data.DataRow           
ID  Name

Update:

I am putting powershell script.

$Database = "temp"
$Server = "localhost"

$AttachmentPath = "output.csv"


# Connect to SQL and query data, extract data to SQL Adapter

$SqlQuery = "select cc.DepartmentID , cc.Name  from HumanResources.Department cc"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$nRecs = $SqlAdapter.Fill($DataSet)
$nRecs | Out-Null

#Populate Hash Table

$objTable = $DataSet.Tables[0]

#Export Hash Table to CSV File

$objTable | Export-CSV $AttachmentPath

I want to remove column header from output.

halfer
  • 19,824
  • 17
  • 99
  • 186
Roxx
  • 3,738
  • 20
  • 92
  • 155

7 Answers7

13

In SSMS Under Tools/Options/Query Results/SQL Server/Results to Text there is a checkbox to 'Include column headers in the result set'. Similar for results to grid.

If you are using sqlcmd via powershell you can use /h-1 to disable the headers.

This setting corresponds to the environment variable SQLCMDHEADERS.

Tips and Tricks

Use a value of -1 to specify that no headers should be printed. If -1 is supplied, there must be no space between the parameter and the setting, that is, -h-1. Otherwise, SQLCMD interprets it as a separate option and fails.

Example (modified from [TechNet])1:

sqlcmd -q /h-1 "SELECT * FROM AdventureWorks2012.Person.Person"

will also work with -h-1

Community
  • 1
  • 1
Karl Kieninger
  • 8,841
  • 2
  • 33
  • 49
  • Thanks for your answer Karl. Could you please show me example for sqlcmd. How it can be done? – Roxx Apr 22 '14 at 14:45
  • sqlcmd -q "SELECT * FROM AdventureWorks2012.Person.Person" -h-1 sqlcmd -q "SELECT * FROM AdventureWorks2012.Person.Person" /h-1 these worked for me. – singhswat Jan 23 '18 at 16:33
4

In management studio at query window right click and select Query options. Look for Result>Text at a tree in the left and check out Include column headers in result set option. I think Hamlet Hakobyan is right, it is client that add column headers.

Surrogate
  • 331
  • 3
  • 11
2

Replace your last line $objTable | Export-CSV $AttachmentPath with

$objTable | ConvertTo-Csv -NoTypeInformation | select -Skip 1 | out-file $AttachmentPath
Nitesh
  • 844
  • 6
  • 10
  • Thanks for your answer. It doesn't solve the issue. I am getting output like this.12Document Control. In a single column. – Roxx Apr 23 '14 at 08:24
  • The solution is working. You are seeing that behavior because of the way Excel is rendering it. Open the CSV using notepad, and you will see how they are properly arranged. That said, i don't understand why you want to remove id and name header. If you remove them, next entry will become header.CSVs contains the properties as first line(header) and then the corresponding values in next lines. If you are looking for how to see the new CSv properly in Excel, try [This](http://help.surveymonkey.com/articles/en_US/kb/Why-is-Excel-showing-the-data-incorrectly-or-all-in-one-column) – Nitesh Apr 23 '14 at 16:06
0

Using the Save As option would not include the attribute (column) names.

sticks
  • 85
  • 1
  • 10
0

This work correctly and column header not exists in out-file:

$workpath = "C:\myworkdir"
$InvSQLParams = @{
    ServerInstance = "SQL2016"
    Database       = "testdb"
    InputFile      = "$($workpath)\selectclause.sql"
}
Invoke-Sqlcmd @InvSQLParams | ConvertTo-Csv -NoTypeInformation | select -Skip 1 | out-file "$($workpath)\result.csv"
JukkaV
  • 61
  • 1
  • 1
-1

in your script, pipe (|) the output to the "tail +3" command. this will skip the first 2 lines of output from the SQL.

user4265768
  • 1
-1

set this after connecting to database SET HEADING OFF

Keynes
  • 109
  • 1
  • 4