Solved

Re-create existing Tables in new DB

Posted on 1997-12-23
12
665 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

In  today’s increasingly digital world, managed service providers (MSPs) fight for their customers’ attention, looking for ways to make them stay and purchase more services. One way to encourage that behavior is to develop a dependable brand of prod…
We have come a long way with backup and data protection — from backing up to floppies, external drives, CDs, Blu-ray, flash drives, SSD drives, and now to the cloud.
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…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

759 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

24 Experts available now in Live!

Get 1:1 Help Now