Remove column header from SQL Server query result

Question

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.

Savoie

Result set can't contain column information, it is just added by your client. – Skip

Thanks for your quick response. When i run the query by powershell script i am getting the column header. Any advise how to remove that. You can see the output below. – Savoie

#TYPE System.Data.DataRow ID Name – Savoie

In your query there is no word about powershell. You must correct your question and add appropriate tags. – Skip

No matter i am using powershell or not. I am getting output like this. – Savoie

Answer 1

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

Adelleadelpho

Thanks for your answer Karl. Could you please show me example for sqlcmd. How it can be done? – Savoie

sqlcmd -q "SELECT * FROM AdventureWorks2012.Person.Person" -h-1 sqlcmd -q "SELECT * FROM AdventureWorks2012.Person.Person" /h-1 these worked for me. – Coeternal

Answer 2

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.

Biology

Answer 3

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

$objTable | ConvertTo-Csv -NoTypeInformation | select -Skip 1 | out-file $AttachmentPath

Tarkany

Thanks for your answer. It doesn't solve the issue. I am getting output like this.12Document Control. In a single column. – Savoie

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 – Tarkany

Answer 4

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"

Rawlings

Answer 5

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

Conservatism

I would like to run the query by script. Save as option doesn't solve my problem. – Savoie

Answer 6

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

Reisfield

Answer 7

set this after connecting to database SET HEADING OFF

Enwreathe

Heading is not a recognized SET option. – Cirenaica

Answer 8

In SQL Server Management Studio (SSMS), there's no direct command to suppress column headers while executing a query. However, you can achieve this by using command-line tools or scripting languages to execute the query and generate the CSV file without headers.

For example, if you're using sqlcmd in a Windows environment, you can execute your SQL query and redirect the output to a file without column headers. Here's an example:

sqlcmd -S your_server_name -d your_database_name -E -s "," -W -Q "SET NOCOUNT ON; select cc.DepartmentID , cc.Name from HumanResources.Department cc" -o output.csv

Explanation of the flags used:

• -S: Server name
• -d: Database name
• -E: Use trusted connection (Windows authentication)
• -s ",": Specify the column separator (comma in this case for CSV)
• -W: Remove trailing spaces
• -Q: Query to execute
• -o: Output file

Another approach would involve using PowerShell or other scripting languages to execute the SQL query and manipulate the output. Here's a PowerShell example:

$Server = "your_server_name"
$Database = "your_database_name"
$Query = "select cc.DepartmentID , cc.Name from HumanResources.Department cc"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $Server; Database = $Database; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $Query
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$DataSet.Tables[0] | Export-Csv -Path "output.csv" -NoTypeInformation

This PowerShell script executes the query, retrieves the data, and exports it to a CSV file without column headers using Export-Csv cmdlet with the -NoTypeInformation parameter.

Remember to replace your_server_name and your_database_name with the appropriate values for your environment.

Both these methods aim to execute the query and generate a CSV file without including column headers in the output.

Enwreathe

Welcome back to Stack Overflow. Since you've been gone a while it may be a good idea to review the site rules, including the one that bans so-called "AI" generated content. – Aweless