Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Re-create existing Tables in new DB

Posted on 1997-12-23
12
Medium Priority
?
714 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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
 
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 240 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
This article will show you step-by-step instructions to build your own NTP CentOS server.  The network diagram shows the best practice to setup the NTP server farm for redundancy.  This article also serves as your NTP server documentation.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

926 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