?
Solved

Re-create existing Tables in new DB

Posted on 1997-12-23
12
Medium Priority
?
709 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

A while back, I ran into a situation where I was trying to use the calculated columns feature in SharePoint 2013 to do some simple math using values in two lists. Between certain data types not being accessible, and also with trying to make a one to…
The core idea of this article is to make you acquainted with the best way in which you can export Exchange mailbox to PST format.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

719 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