Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2006-10-25
2
Medium Priority
?
1,507 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 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

The decision to migrate to the cloud is not a simple one—many factors, such a cost, ease of use, and ongoing maintenance come into play. The goal is always for cloud platforms to make storage and backups more seamless and effective. Here’s a look at…
Do online retailers and e-commerce sites even need to bother with the season? Is it lucrative to update and prepare your site for Black Friday and Cyber Monday shoppers? We'll find out now.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

772 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