Solved

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

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

HOW TO: Upload an ISO image to a VMware datastore for use with VMware vSphere Hypervisor 6.5 (ESXi 6.5) using the vSphere Host Client, and checking its MD5 checksum signature is correct.  It's a good idea to compare checksums, because many installat…
A procedure for exporting installed hotfix details of remote computers using powershell
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now