CODEWITHSUNDEEP
sql-serversql-server-2008powershellsqlcmd
Roxx
Roxx

Reputation: 3986

Remove column header from SQL Server query result

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.

Upvotes: 7

Views: 82082

Answers (8)

Keynes
Keynes

Reputation: 137

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.

Upvotes: -1

JukkaV
JukkaV

Reputation: 81

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"

Upvotes: 0

Keynes
Keynes

Reputation: 137

set this after connecting to database SET HEADING OFF

Upvotes: -1

user4265768
user4265768

Reputation: 1

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

Upvotes: -1

Nitesh
Nitesh

Reputation: 874

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

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

Upvotes: 2

Karl Kieninger
Karl Kieninger

Reputation: 9129

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

Upvotes: 16

sticks
sticks

Reputation: 83

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

Upvotes: -1

Surrogate
Surrogate

Reputation: 331

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.

Upvotes: 4

Related Questions