How to export a table with a header using bcp

demo

I swear that's what was posted.  Mostly they want a header row in their data and they didn't want to use DTS...
bcp out with column names post
OK, here you go..probably need to deal with more datatype than I have accounted for...

1. USE Northwind
   
2. GO
   
3. 
   
4. SET NOCOUNT ON
   
5. 
   
6. DECLARE @sql1 varchar(8000), @sql2 varchar(8000), @TABLE_NAME sysname, @TABLE_SCHEMA sysname
   
7. 
   
8. DECLARE myCursor99 CURSOR
   
9.   FOR
   
10. SELECT TABLE_SCHEMA, TABLE_NAME
    
11.    FROM INFORMATION_SCHEMA.Tables
    
12.   WHERE TABLE_TYPE = 'BASE TABLE'
    
13.     AND TABLE_NAME LIKE 'O%'
    
14. 
    
15. OPEN myCursor99
    
16. FETCH NEXT FROM myCursor99 INTO @TABLE_SCHEMA, @TABLE_NAME
    
17. 
    
18. WHILE @@FETCH_STATUS = 0
    
19.   BEGIN
    
20. 
    
21. SELECT @sql2 = ' UNION ALL SELECT ', @sql1 = null
    
22. 
    
23. SELECT @sql1 = COALESCE(@sql1 + ', '+''''+'"'+''''+'+'+'''','') + COLUMN_NAME + ''''+'+'+''''+'"'+''''+' AS '+ COLUMN_NAME
    
24.    FROM INFORMATION_SCHEMA.Columns
    
25.   WHERE TABLE_NAME = @TABLE_NAME
    
26.     AND TABLE_SCHEMA = @TABLE_SCHEMA
    
27. 
    
28. SELECT @sql2 = CASE
    
29.        WHEN DATA_TYPE IN ('datetime','smalldatetime')
    
30.        THEN @sql2 + ', '+''''+'"'+''''+'+CONVERT(varchar(24),' + COLUMN_NAME + ',126)+' + ''''+ '"' + '''' + 'AS ' + COLUMN_NAME
    
31.        WHEN DATA_TYPE IN ('bigint','int','smallint','tinyint','money','float','real')
    
32.        THEN @sql2 + ', '+''''+'"'+''''+'+CONVERT(varchar(15),' + COLUMN_NAME + ')+' + ''''+ '"' + '''' + 'AS ' + COLUMN_NAME
    
33.        WHEN DATA_TYPE IN ('char','varchar','nchar','nvarchar')
    
34.        THEN @sql2 + ', '+''''+'"'+''''+'+' + COLUMN_NAME + '+' + ''''+ '"' + '''' + 'AS ' + COLUMN_NAME
    
35.          END
    
36.    FROM INFORMATION_SCHEMA.Columns
    
37.   WHERE TABLE_NAME = @TABLE_NAME
    
38.     AND TABLE_SCHEMA = @TABLE_SCHEMA
    
39. 
    
40. SELECT @sql1 = 'CREATE VIEW ' + REPLACE('['+@TABLE_SCHEMA+'].[v_'+@TABLE_NAME+'_99]',' ','_')
    
41.    + ' AS SELECT '+''''+'"'+''''+'+'+''''
    
42.    + @sql1 + REPLACE(@sql2,'UNION ALL SELECT ,','UNION ALL SELECT ')
    
43.    + ' FROM ['+@TABLE_SCHEMA+'].['+@TABLE_NAME+']'
    
44. 
    
45. SELECT @sql1
    
46. 
    
47. EXEC(@sql1)
    
48. 
    
49. -- SELECT * FROM myView99
    
50. 
    
51. DECLARE @cmd varchar(8000)
    
52. 
    
53. SELECT @cmd = 'bcp ' + db_name() + '.' + REPLACE('['+@TABLE_SCHEMA+'].[v_'+@TABLE_NAME+'_99] ',' ','_')
    
54.    +' OUT '
    
55.    +REPLACE(@TABLE_SCHEMA+'_'+@TABLE_NAME,' ','_')
    
56.    +'_hdr.txt -c -S -Usa -P'
    
57. SELECT @cmd
    
58. EXEC master..xp_cmdshell @cmd
    
59. 
    
60. SELECT @sql2 = 'DROP VIEW ' + REPLACE('['+@TABLE_SCHEMA+'].[v_'+@TABLE_NAME+'_99]',' ','_')
    
61. EXEC(@sql2)
    
62. 
    
63. FETCH NEXT FROM myCursor99 INTO @TABLE_SCHEMA, @TABLE_NAME
    
64.   END
    
65. CLOSE myCursor99
    
66. DEALLOCATE myCursor99
    
67. GO
    
68. 
    

复制代码

http://weblogs.sqlteam.com/brettk/archive/2005/04/13/4395.aspx