从 SQL Server 查询结果中删除列标题 [英] Remove column header from SQL Server query result

问题描述

我想从 SQL Server 查询输出中删除列标题.我进行了搜索，但没有找到任何解决方案.我有一个查询，例如.

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

我想从 SQL Server 的输出中删除 ID 和名称(列标题).

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

我将通过脚本运行此查询以生成 csv 文件.

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

当我通过脚本运行查询时，我得到了 csv 文件作为输出，它看起来像这样.

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    

更新:

我正在放置 powershell 脚本.

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.

推荐答案

在工具/选项/查询结果/SQL Server/结果转文本下的 SSMS 中，有一个在结果集中包含列标题"复选框.结果与网格类似.

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.

如果您通过 powershell 使用 sqlcmd，您可以使用/h-1 来禁用标头.

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

此设置对应于环境变量 SQLCMDHEADERS.

This setting corresponds to the environment variable SQLCMDHEADERS.

提示和技巧

使用 -1 的值来指定不应有任何标题打印.如果提供了 -1，则两者之间不得有空格参数和设置，即-h-1.否则，SQLCMD 解释它作为一个单独的选项并失败.

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.

示例(从 [TechNet] 修改)1::>

Example (modified from [TechNet])1:

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

也适用于 -h-1

这篇关于从 SQL Server 查询结果中删除列标题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！