Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>This method automatically outputs column names with your row data using <a href="https://msdn.microsoft.com/en-au/library/ms162802.aspx" rel="noreferrer">BCP</a>.</p> <p>The script writes one file for the column headers (read from <code>INFORMATION_SCHEMA.COLUMNS</code> table) then appends another file with the table data.</p> <p>The final output is combined into <code>TableData.csv</code> which has the headers and row data. Just replace the environment variables at the top to specify the Server, Database and Table name.</p> <pre><code>set BCP_EXPORT_SERVER=put_my_server_name_here set BCP_EXPORT_DB=put_my_db_name_here set BCP_EXPORT_TABLE=put_my_table_name_here BCP "DECLARE @colnames VARCHAR(max);SELECT @colnames = COALESCE(@colnames + ',', '') + column_name from %BCP_EXPORT_DB%.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='%BCP_EXPORT_TABLE%'; select @colnames;" queryout HeadersOnly.csv -c -T -S%BCP_EXPORT_SERVER% BCP %BCP_EXPORT_DB%.dbo.%BCP_EXPORT_TABLE% out TableDataWithoutHeaders.csv -c -t, -T -S%BCP_EXPORT_SERVER% set BCP_EXPORT_SERVER= set BCP_EXPORT_DB= set BCP_EXPORT_TABLE= copy /b HeadersOnly.csv+TableDataWithoutHeaders.csv TableData.csv del HeadersOnly.csv del TableDataWithoutHeaders.csv </code></pre> <p>Note that if you need to supply credentials, replace the -T option with -U my_username -P my_password</p> <p>This method has the advantage of always having the column names in sync with the table by using <code>INFORMATION_SCHEMA.COLUMNS</code>. The downside is that it creates temporary files. Microsoft should really fix the bcp utility to support this.</p> <p>This solution uses the SQL row concatenation trick from <a href="https://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string">here</a> combined with bcp ideas from <a href="http://social.msdn.microsoft.com/forums/en-US/sqlgetstarted/thread/812b8eec-5b77-42a2-bd23-965558ece5b9/" rel="noreferrer">here</a></p>
    singulars
    1. This table or related slice is empty.
    1. POexport table to file with column headers (column names) using the bcp utility and SQL Server 2008
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USDylan Hogg
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI use `select substring(@colnames,2,1000000000);` instead of `select @colnames;` because the `@colnames` variable is printed with a coma at the begining of the headers.
      singulars
      1. PO
      1. USGalled
    2. COAwesome script dude. I would vote up twice if I could. Any way to make the names of the tables and databases as batch script variables?
      singulars
      1. PO
      1. USCrazyTim
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload