If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

P.S. I don't delieve there is a way to dump a database without data or just the schema. This is normally done by reverse engineering your database (generating DDL scripts) using IDE and/or IAE tools (DB Artisan is a good example, ERWin, etc.) or by keeping the DDL scripts you used originally for the production rollout (... of course maintaing the scripts as objects are added or dropped).

Dumping a databse without data

Thanks for your reply

Yes what I have realize is that too but their should be a way right what does bcp or Bulk Copy for imports table data from flat files and exports data out to files do u aware about this can you explain me how to carry out on this, creating DDL under each and every table can be done but I have around 150 tables so I need them with the data that cant be done if I use DDL

Using an IDE / IAE Tool is the most efficiant and best way...

Sure, I'll give it my best shot to explain the best I can...

BCP tools and/or utilities are used to bulk copy data "out" and/or "in" from a table to a flat file (or vise versa)... Or copy data from a table on one server to another table on another server. Please keep in mind, this is only copying data out in a deliminated format. In no way is it copying out the schema or DDL information with it. SQSH is a great tool for this (a very powerful utility with both isql and bcp built into one).

This is where the database option... "Allow Bulk Copy" comes in. It allows you to use BCP utilities and copy data in/out of a table in that specific database. If this database option is not set / not enabled, the BCP will fail.

Yet, this doesn't solve or address your problem. This is something you will do after creating the destination database and desired tables/objects.

------------------------------------------------

Okay, so this brings us to tying to find the best way to address your challenge... getting the DDL or schema for your database tables in the most efficiant way...

Since you have so many tables, your best bet is using an IDE and/or IAE (i.e. DB Artisan, ERWIN, or any tool that has database reverse engineer funtionality). Keep in mind though, you are putting a lot of trust into the tool, hoping that it spits everything out correctly. Personally, I would use one of these tools and then comb through the DDL that it generates to confirm that all looks good.

Oh... I just came up with another idea. The second idea is to create a Perl or Korn Shell script that loops a call to the sp__revtable stored procedure (free stored procedure at Ed Barlow's website), using the sysdatabases system table as the loop critiria. Have the isql spit out the results to an output file. Actully, the more I think, the more I realize that there's a million ways to do this. I'm sure a person can think of a "million ways to skin a cat".... So the short answer is...

Option 01 - Create a script that calls a procedure that can reverse engineer the table schema and output the DDL (i.e. use a combination between Ed Barlow's sp__revtable and sp__revdb stored procedure, isql and your prefered handy dandy scripting language).

Option 02 - Use a IDE / IAE that can reverse engineer you database and output the DDL to a file.

Optoin 03 - Pay someone off the street to do it for you, but don't tell anyone you did so.

Anyway, any way you look at it... you are going to have to pay the price somewhere (in man hours or the price of a good DBA Software Suite).

bcp is not a sql command. It should be executed from the operating system command line.
Use the -U and -P flags to specify user name and password
And if the DSQUERY environment variable is not set to the value you need use the -S flag to specify the sybase server name
see Adaptive Server&#174; Enterprise 15.0 Utility Guide
Chapter 8: Utility Commands Referencebcp

Good to see you had success
Creating a format file can be a pain
An alternative is to create a view on the source to convert it to look like the target table then bcp out from the view using -n or -c switch e.g.
bcp avameddb..ORG_TO_CLI_VIEW out org2cli.dat -Usa -SAVAMEDDB -n
bcp avameddb..IC_CLI_TRUNK in org2cli.dat -Usa –SSYBASEASE -n

And for improved speed, if our Sybase server is configured to allow a larger network packet size add e.g. -A8704