Re-create existing Tables in new DB

Hiya........
I want to recreate ALL full tables as empty tables
from one database into a new database. These tables DO NOT exist in the new database. There are 400 of them and I don't want to create them manually. The defncopy and bcp utilities don't help here, as I want to create the tables then run these utilities on the database afterwards.
Reason....
I want to re-create an exact replica of the database in
a smaller version of it, for use at home where I haven't
got the disk space.

This is not an easy one but the only points
I got left are 62.45pts.

regards

P.S. If in your opinion it can't be done. Please do not supply it as an answer, but as a comment. You never know there might be a way of doing this (i've got part of the
way using a script that I wrote)? then that would leave the question unlocked for the others to see instantly.
LVL 3
rickyrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rickyrAuthor Commented:
There, I'm cleaned out!
0
rickyrAuthor Commented:
Edited text of question
0
SrwCommented:
Now *I'm* confused.  I thought defncopy copies the object definitions (DDL), while bcp copied the data.

Use defncopy to get the object definitions out, and use the same to put them in at home.  Likewise for the data with bcp.

You can just query the sysobjects table to get a list of the database tables you have.
0
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

RONYCommented:
You didn't say what db you have, so i can only supply you an anwser for sybase 'SQL ANYWHERE'
In sybase it is quit simple to do this.
1. You unload your database, where you specify you only want to unload the structures and NOT the data. This can be done with SQL central or dbunload. The unload will create a file called 'unload.sql'.
2.At home you use ISQL to load your file and it will recreate all your tables, indexes, stored procedures....
Hope this helps
0
bretCommented:
defncopy does not copy out table definitions, only views, rules, defaults, triggers, and procedures.  However, there are other third-party utilties that can handle table definitions.  See the "Shareware"section of the comp.databases.sybase newsgroup FAQ at http://reality.sgi.com/pablo/Sybase_FAQ.

Some products, like SA Companion and (I believe) DBArtisian can
also extract the whole database schema.
0
rickyrAuthor Commented:
Hi..

My SYBASE is SQL Server 10.X running on a Sparc10
with Solaris 2.5

srw....
To use defncopy and bcp. The tables have to be there already, bcp also will add data to tables that already have data without overwriting it.

RONY....
There doesn't appear to be an "unload" database command.

bret...
I didn't want to reject your answer, I just wanted to clarify
a few points to help you to help me.

The 2 products you mention look like they are expensive, as this is for home use, can't afford it.

The info from the pages suggest that the best option is to use sybperl to generate a schema of my db. A friend has already tried this and it didn't work.

here is what I got on the backburner...


#amcl is called by data
#!/bin/sh
if [ $# -ne 1 ] ; then
echo usage: amcl '"sql statements"'
exit
fi
isql -Umidsys -Pmidsys -SSYBASE-S2 <<! | sed -e '/^$/d' -e '1,3d' -e '/rows affected/d'
$1
go
!

##script name=data
##Script to get the table struct , from Tables
##that contain info on tables
#!/bin/sh -xv
if [ -f tablelist -o -f gendb ] ; then
rm tablelist rm gendb
fi

echo '#!/bin/sh' > gendb
echo 'isql -Umidsys -Pmidsys <<!' >> gendb
echo use reprodb >> gendb
echo go >>gendb

amcl "SELECT DISTINCT TABLE_NAME FROM COLUMNDATA" | nawk '{ print $1 }' > tablelist
sleep 3
tblcnt=`cat tablelist | wc -l`
counter=1
while [ $counter -le $tblcnt ]
do
table=`cat tablelist | sed -n ''$counter'p'`
echo create table "${table}" >> gendb
echo \( >> gendb
amcl "SELECT COLUMN_NAME, DATATYPE_NAME (print ",") FROM DATATYPE, COLUMNDATA WHERE COLUMNDATA.DATATYPE_ID = DATATYPE.DATATYPE_ID AND TABLE_NAME = '${table}'" | nawk '{ print $1,$2,$3}' >> gendb
echo \) >> gendb
echo go >> gendb
counter=`expr $counter + 1`
done
echo '!' >> gendb
chmod u+x gendb

##gendb is the name of the new script to generate the db


OK phew.
bottom line is that this script fails because the data types
don't exist. There are hundreds of them and I don't want to generate these manually. If I'm begining to put people off, sorry.

regards.


0
rickyrAuthor Commented:
Woops
Change the $3 in the nawk part of the second "amcl" call to ",".
apologies
0
bretCommented:
Is your problem that you have many user-defined datatypes?

If so, then bcp out the systypes table from the database and bcp it into the new database.  Use the "-b1" parameter on the bcp in
so it does each row as a seperate transaction.  It will kick out the duplicate (ie standard datatype) rows, but bcp in everything else.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jbiswasCommented:
This can certtainly be done by DBArtisan. Unload a shareware version of DBArtisan from the embarcadero site and then doing this is like 1-2-3. Do an extract DDL for entire database and choose schema only. Exclude the data. All table, views, triggers, procedures, datatypes.....and all else just get extracted into a reload script. I have done this a million times and it has never failed me. The only place it may fail is if stored procedures call other stored procedures. IN that case open the script manually and create the dependent stored procedure by simply executing the script from isql. If all goes well this could be over in less than an hour. I f it doesn't or you are experiencing any problems e-mail me at jbiswas@indcon.com. Dbartisan is available at http://www.embarcadero.com. Thanks
0
dayalCommented:
Hi if you have data model tools ,you can use S-DESIGNOR or ER-Win datamodel tools to create the table structures.use  S-DESIGNOR reverse engineering to create all the tables DDL.

S-DESIGNOR is a GUI based tool connect to the server, select the database and select all the tables from that database and click to button to create.

Login in to the new database and run this script in your new database all the table will be created without data.
0
rickyrAuthor Commented:
hi bret...
After reading jbiswas's comment (thanx jbiswas) I found where to get DBartisan that you mentioned. I did what you said and it did indeed replicate my systypes table in the new db.

DBArtisan looks brilliant, but complains that I do not have LIBSYBDB.DLL. I guess this is because I don't have the Client-Library suite for SQL Server. Is this software going to
cost me a fortune, or is there anyway to download these from
an online source?

Happy New Year, One and All
0
jbiswasCommented:
hey I should have got those points after all  :-)))
No DBArtisan does not cost a fortune it sells for $895. However since you're doing this at home maybe it's not such a good idea to purchese it. Why don't you ask your company to purchase it????
...and no I do not work for embarcadero..just find the product really kewl.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.