SQL Server – How to Get Headers from SQL Table

bcpexportsql-server-2012table

I am looking for a way to dynamically get the headers from a table without having to write them (select 'header1' as "header1"…., 'header50' as "header50" from MyTable)
It doesn't work above a certain number of columns, and/or the fixed limit of characters in a query

The goal is to export the headers and the data from the table to a text file or an excel file with a BCP command.

Can you help me out, please ?

Best Answer

If using xp_cmdshell and Powershell is an option (rather than BCP), you could achieve your goal as follows:

--Demo setup to create and load a table with some data
SET NOCOUNT ON
DROP TABLE IF EXISTS [dbo].[CUSTOMER];
CREATE TABLE [dbo].[CUSTOMER](
    [CustomerID] [int] NOT NULL,
    [FirstName] [varchar](20) NOT NULL,
    [LastName] [varchar](20) NOT NULL,
    [Email] [varchar](30) NOT NULL,
    [PhoneNo] [int] NOT NULL,
    [StreetAddress] [varchar](40) NULL,
    [City] [varchar](20) NULL,
    [OrderID] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[CUSTOMER] ([CustomerID], [FirstName], [LastName], [Email], [PhoneNo], [StreetAddress], [City], [OrderID]) VALUES (13579246, N'David', N'Casey', N'dcasey@gmail.com', 12826363, N'34 Bray Rd', N'Dublin', 94724274)
GO
INSERT [dbo].[CUSTOMER] ([CustomerID], [FirstName], [LastName], [Email], [PhoneNo], [StreetAddress], [City], [OrderID]) VALUES (16579946, N'Donal', N'Smith', N'dsmith@gmail.com', 13826563, N'78 Ballybrack Rd', N'Dublin', 14724274)
GO
INSERT [dbo].[CUSTOMER] ([CustomerID], [FirstName], [LastName], [Email], [PhoneNo], [StreetAddress], [City], [OrderID]) VALUES (17579946, N'Steve', N'Reidy', N'sreidy@gmail.com', 13827563, N'22 Conna Rd', N'Cork', 64724274)
GO
INSERT [dbo].[CUSTOMER] ([CustomerID], [FirstName], [LastName], [Email], [PhoneNo], [StreetAddress], [City], [OrderID]) VALUES (18579946, N'Michael', N'Yule', N'myule@gmail.com', 14827563, N'44 Blasket Rd', N'Kilkenny', 44424274)
GO

A possible downside to this approach is because ConvertTo-CSV automatically surrounds output columns with double quotes. The follow script handles the elimination of ALL double quotes. This could be problematic if your data actually contains double quotes.

--The solution
--The output goes to C:\Test\test.txt
--Make sure that the parameter value for -ServerInstance below is equal to the Server Name
--of the SQL Server instance you want to access.  The example below is using a single period 
--which indicates the default instance on the computer where the Powershell script is actually running.

DECLARE @cmd VARCHAR(4000)
set @cmd = 'powershell.exe "Invoke-Sqlcmd -ServerInstance . -Query ''Select * from Test.dbo.customer'' | ConvertTo-Csv -NoTypeInformation -Delimiter "`t"  | % {$_ -replace ''""'',''''} | Out-File (''C:\Test\test.txt'') "' 
print @cmd
exec xp_cmdshell @cmd

Note that the ConvertTo-Csv cmdlet allows you to specify a Delimiter. If you don't provide that parameter, the default is a comma (as represented by the output below.

Results in C:\Test\test.txt:

CustomerID,FirstName,LastName,Email,PhoneNo,StreetAddress,City,OrderID
13579246,David,Casey,dcasey@gmail.com,12826363,34 Bray Rd,Dublin,94724274
16579946,Donal,Smith,dsmith@gmail.com,13826563,78 Ballybrack Rd,Dublin,14724274
17579946,Steve,Reidy,sreidy@gmail.com,13827563,22 Conna Rd,Cork,64724274
18579946,Michael,Yule,myule@gmail.com,14827563,44 Blasket Rd,Kilkenny,44424274

Here is an example where I'm using backtick t (`t) to export a tab delimited file.

DECLARE @cmd VARCHAR(4000)
set @cmd = 'powershell.exe "Invoke-Sqlcmd -ServerInstance . -Query ''Select * from Test.dbo.customer'' | ConvertTo-Csv -NoTypeInformation -Delimiter "`t"  | % {$_ -replace ''""'',''''} | Out-File (''C:\Test\test.txt'') "' 
print @cmd
exec xp_cmdshell @cmd
Related Question