0

To obtain a list of all tables from all data marts in a data warehouse using Sybase, we use

select * from sysobjects where type = 'U'

However, I want to grab and export all tables from a particular data mart ("DM"). I'm relatively new to Sybase, so my gut's telling me to do something along the following lines:

select * from DM.sysobjects where type = 'U'

That's obviously not working, or else I would not be here :). Can someone point me in the right direction?

blacksite
  • 12,086
  • 10
  • 64
  • 109
  • Sybase was a company, not a database. Can you specify which product/version you are using? (ASE, IQ, SQL Anywhere, etc) Answers will vary based on what you are using. – Mike Gardner Jul 15 '15 at 19:13

1 Answers1

0

Based on the tables you mention, it looks like you are using Sybase ASE.

Sybase ASE doesn't support exporting all tables at once automatically through any method besides doing a dump of the database. To get all the tables in a database, you will need to create a script that will loop through all the tables in a database (what you are calling data mart).

There are many different ways to go about this, and it can vary based on if you are on a *nix or windows platform, but this will at least give you an idea.

set nocount on
select "bcp "+ name + " out " + name + ".bcp -Uusername -Sservername -Ppassword -c
from sysobjects where type = 'U'
go

This will give you a script file that should be easy to run against the server. -c denotes that it's a character mode or plain text file. There are other options for changing delimiters, and more but this should get you started.

This answer has more details on using bcp

Community
  • 1
  • 1
Mike Gardner
  • 6,611
  • 5
  • 24
  • 34