?
Solved

extracting the table definition from one tabke and create same tables on another database

Posted on 2006-10-25
2
Medium Priority
?
1,500 Views
Last Modified: 2008-01-09
Hi All
I have a requirement where in I need to create a bunch of tables in a database. I am getting the table defnitions from 5 different databases and creating the same into this newly created database.

example: lets assume there are 3 databases

Database= A (with tables: X and Y),
Database = B (with table: P and Q)

Now I want to create those tables X, Y, P and Q into the Database C. If I do it manually, I get the table definitions from the databases A and B and create them manually in Database C. Is there any script to do this automatically. So that I can give the following to that script

Source Database:
List of tables in Source DB
Destination DB.

So that the script can go to Source Db, get the table definition in that and create the same tables in Destination database.

any help is appreciated.

Thanks
0
Comment
Question by:dhuma
[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
2 Comments
 
LVL 19

Accepted Solution

by:
grant300 earned 750 total points
ID: 17807966
If the databases are on the same Sybase server, you can do it very easily.  The indexes are a bit tougher.

You can create the table definitions like so:

USE C
GO
SELECT * INTO X FROM A..X WHERE 1 = 2
GO
SELECT * INTO Y FROM A..Y WHERE 1 = 2
GO
SELECT * INTO P FROM B..P WHERE 1 = 2
GO
SELECT * INTO Q FROM B..Q WHERE 1 = 2
GO

The other way to do this is to fire up Sybase Central (distributed with Sybase) and extract the DDL for the tables (and keys and indexes) and save them to a file.  You can then edit the USE statements and then run the SQL script into database 'C' using ISQL.

One more thing.  It sounds like you are not using any of the tools that are available.  Sybase Central is kind of a DBA tool rather than a developer tools but you really should not try to run a database without it.  Second, there is a very good, very cheap general purpose SQL developers tool called Aqua Data Studio  (go to www.aquafold.com)  It is free for evaluation and "educational" use and something like $149 bucks for a single user commercial license.  If you like the tool on eval, pop for the license.  It will save you many times it's cost in the first week.

Regards,
Bill
0
 

Expert Comment

by:PegasusAloft
ID: 17808012
You didn't specify what version or type of Sybase you were running (ASE or ASA) but this might be a starting point to get you in the right direction:

select A.Table_name, B.column_name,
       (Select domain_name from SYSDOMAIN where domain_id = B.domain_id) as DataType,
        B.width as DataWidth,
        B.scale as DataScale,
       (CASE Pkey
        when 'Y' then 'YES'
        else NULL
        END) as Pkey
from SYSTABLE A, SYSCOLUMN B
where B.table_id = A.table_id and A.Table_Name NOT LIKE ('SYS%')
order by Table_Name, column_id;

This will grab all of the tables that don't begin with SYS and give you a column layout. It should be easy to modify the script to suit your purposes to dynamically generate the tables (IF EXISTS test on database C to see if it needs to be generated or not).

Probably an easier to go into Sybase Central and generate an UNLOAD script on each database, bring it over to server C and run it in iSQL. Simply "continue" through any errors where the table already exists to move on to the next CREATE TABLE statement.



0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

This is a fine trick which I've found useful many times, when you just don't want to accidentally run a batch script or the commands needs administrator rights.
We are witnesses that everyone is saying that our children shouldn't "play" with a technology because it is dangerous. This article is going to prove that they are wrong.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

800 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