Solved

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

Posted on 2006-10-25
2
1,478 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

If you thought ransomware was bad, think again! Doxware has the potential to be even more damaging.
There’s a good reason for why it’s called a homepage – it closely resembles that of a physical house and the only real difference is that it’s online. Your website’s homepage is where people come to visit you. It’s the family room of your website wh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

809 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