SQL Server 2008-使用cmd输出带标头的.csv [英] SQL Server 2008 - use cmd to output with headers to .csv

问题描述

我有一个非常简单的问题(这些通常是我大部分时间都在梳理头发的问题).我正在使用一个批处理文件来执行与该批处理位于同一目录中的所有.sql查询，并将所有结果保存到各种.csv文件中.

I have a pretty simple question (and these are typically the ones I spend most of my time tearing my hair out about). I am using a batch file to execute all the .sql queries that are in the same directory as the batch, and to save all their results to various .csv file.

这是我的代码:

@echo off

REM Check that all parameters are present
@IF "%1"=="" goto error
@IF "%2"=="" goto error
@IF "%3"=="" goto error
@IF "%4"=="" goto error

REM For every *.sql file in this folder "." and all its subfolders (/R), do the following: 
@for /R "." %%F in (*.sql) do (
REM Print the command to be executed:
@echo Database : @SqlCmd -S %1 -U %2 -P %3 -d %4 -I -l60 -i "%%F" -o "%%F.output.csv"  -h-1 -s"," -w 1000 -W
REM Execute the command:
@SqlCmd -S %1 -U %2 -P %3 -d %4 -I -l60 -i "%%F" -o "%%F.output.csv" -h-1 -s"," -w 1000 -W
)

goto :EOF

:error

@echo Incorrect syntax : 
@echo     extract.cmd [servername] [user id] [password] [databasename]
@echo   
@pause

goto :EOF

作为测试，我运行以下查询:

As a test, I run the following query:

select top(10) [name] from sysobjects 

输出:

sysrscols
sysrowsets
sysallocunits
sysfiles1
syspriorities
sysfgfrag
sysphfg
sysprufiles
sysftinds
sysowners

(10 rows affected)

这很好，除了两件事.首先，我还需要输出列标题.因此，我删除了"-h -1"参数，该参数给出了:

This works fine, except for two things. Firstly, I need to have the column headers output as well. So I remove the "-h -1" parameter, which then gives:

name
----
sysrscols
sysrowsets
sysallocunits
sysfiles1
syspriorities
sysfgfrag
sysphfg
sysprufiles
sysftinds
sysowners

(10 rows affected)

很棒，除了我不希望标题和数据的第一行之间有水平线"------".

which is great except for the fact that I don't want the horizontal rule "------" between the heading and the first row of data.

我也不希望输出((受影响的10行)")行.

Also, I don't want the "(10 rows affected)" line to be output.

有人知道我该怎么做到吗?

Does someone know how I could achieve this?

谢谢
卡尔

推荐答案

尝试一下:

SET NOCOUNT ON;
SELECT
    [Name]
    FROM (SELECT TOP(10)
              2 AS SortBy, [name]
              FROM sysobjects 
          UNION
          SELECT 1, 'Name'
         ) dt
    ORDER BY [Name]

从SSMS输出:

Name
----------------------------------------------
Name
sysallocunits
sysfiles1
sysftinds
syshobtcolumns
syshobts
sysowners
sysprivs
sysrowsetcolumns
sysrowsets
sysserefs

保留"-h -1"参数，将删除列标题，但名称"行仍将首先出现在结果集中.

leave the "-h -1" parameter and the column headings will be removed, but the "Name" row will still appear in the result set first.

这篇关于SQL Server 2008-使用cmd输出带标头的.csv的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！