Solved

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

Posted on 2006-10-25
2
1,484 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
2 Comments
 
LVL 19

Accepted Solution

by:
grant300 earned 250 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

Question has a verified solution.

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

This article describes how to reset your Windows 10 password when you've forgotten it.
There's a lot of hype surrounding blockchain technology. Here's how it works and some of the novel ways it' s now being used - including for data protection.

860 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