How to export Sybase table contents to a text file

Question

I want to export the tables with contents from a Sybase database to a text file. I am using Sybase ASE 15.5 and that can't be changed/upgraded.

I have tried using sp_tables and different Select commands, but they don't give the result I'm looking for.

The output format I'm looking for is something like this:

FIRST_TABLE

Column_1 Column_2 Column_3

Roger    Male     51

Anne     Female   46

SECOND_TABLE

Column_1 Column_2 Column_3

BMW      German   Car

Schwinn  American Bicycles

etc.etc.

Answer 1

Create a view that generates the output you want and use bcp to copy the data from the view.

Consider the following table, view and data:

create table t1 (
    k       int not null,
    v       varchar(255) null)
go

create view v1 as
select
        'k' as k,
        'v' as v
union all
select
        convert(varchar, k),
        v
    from
        t1
go

insert into t1 (k, v) values (1, 'Line_1')
insert into t1 (k, v) values (2, 'Line_2')
insert into t1 (k, v) values (3, 'Line_3')
go

Check the data returned from the view, notice the column names are in the result set. They need to here. Ideally you would query against syscolumns, but there is no pivot statement in ASE, so you need to know the names in advance :-(

select * from v1
go

k   v
1   Line_1
2   Line_2
3   Line_3
(4 rows affected)

Now copy the data from the view into the text file:

$ bcp <db_name>..v1 out v1.txt -c -U login_name -S server_name
Password: 


Starting copy...

4 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1      Average : (4000.0 rows per sec.)

$ cat v1.txt
k   v
1   Line_1
2   Line_2
3   Line_3

Answer 2

Without using BCP

declare cur cursor for 
  select sc.name from sysobjects so, syscolumns sc
  where  so.id = sc.id and so.name = 'FIRST_TABLE'
  order by sc.colid
go
declare @l_name varchar(30), @l_sql varchar(2000)
begin
  open cur
  fetch cur into @l_name
  while @@sqlstatus = 0
  begin
    set @l_sql = @l_sql + '''' + @l_name + ''','
    fetch cur into @l_name
  end
  close cur
  deallocate cur
  set @l_sql = 'select ' + substring(@l_sql,1,len(@l_sql)-1) 
  set @l_sql = @l_sql + ' union all select * from FIRST_TABLE'
  exec (@l_sql)
end  
go