Solved

Re-create existing Tables in new DB

Posted on 1997-12-23
12
668 Views
Last Modified: 2008-02-01
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.
0
Comment
Question by:rickyr
  • 5
  • 2
  • 2
  • +3
12 Comments
 
LVL 3

Author Comment

by:rickyr
ID: 1098138
There, I'm cleaned out!
0
 
LVL 3

Author Comment

by:rickyr
ID: 1098139
Edited text of question
0
 
LVL 2

Expert Comment

by:Srw
ID: 1098140
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
 

Expert Comment

by:RONY
ID: 1098141
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
 
LVL 10

Expert Comment

by:bret
ID: 1098142
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
 
LVL 3

Author Comment

by:rickyr
ID: 1098143
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 3

Author Comment

by:rickyr
ID: 1098144
Woops
Change the $3 in the nawk part of the second "amcl" call to ",".
apologies
0
 
LVL 10

Accepted Solution

by:
bret earned 60 total points
ID: 1098145
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
 
LVL 2

Expert Comment

by:jbiswas
ID: 1098146
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
 

Expert Comment

by:dayal
ID: 1098147
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
 
LVL 3

Author Comment

by:rickyr
ID: 1098148
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
 
LVL 2

Expert Comment

by:jbiswas
ID: 1098149
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What does UTC stand for?  “Coordinated Universal Time” – Think of this as the true time on Planet Earth that never changes with the exception of minor leap seconds here and there to account for the changes in the planet's rotation.   What does th…
Note: This is the second blog post in a series on email clearinghouses (https://www.xmatters.com/alert-management/blog-email-has-failed-us?utm_campaign=70138000000ydLoAAI&utm_source=exex&utm_medium=article&utm_content=blog-post).   Every month t…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now