We help IT Professionals succeed at work.

I need a script to extract all database objects structure

Ramez
Ramez asked
on
Hey Sybase Experts,
I need a script to extract all database objects structure as we creating a new tool that archiving database ,, This tool should scan the source database for any new objects before archiving and if there is any new object, it should extract its structure to create it on the destination database (The Archiving database).
So how can I extract any object sructure, is there any system view like in Oracle?

Thanks in advance
Ramez Nabil
Comment
Watch Question

CERTIFIED EXPERT

Commented:
Hi

AFAIK there is no direct way to do it. There are some tools like sybase central ,aqua data studio which can pull out defintion of objects.

If you wanna go ahead in script direction the following could help

1) find out new/changed  objects from sysobjects ,syscolumns etc
2) for views,triggers and storedprocs you could use defncopy to pull out the defntion and you would have to add some logic edit the defintion to add go, drop if exists etc.

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.utility/html/utility/utility205.htm

3)Tables are more tricky as it has data also in it . So if both databses are in same server I would suggest doing a select into archdb..<table> from maindb..<table>

Otherwise(I wouldnt suggest this one) you might have to write a script to make the create table statement using data in sysobjects,syscolumns,sysindexes,systypes etc. Refer the subsytemproc sp_help to get a idea.Once table is created you will have to load data using bcp

hope this helps

Good luck
Maneksh
CERTIFIED EXPERT

Commented:
Hi,

Once I was half way wrting this for tables  had reached until the below query to get the defs
Found it back in my archives . Guess this would give u a headstart if you want to go in the complex direction

declare
      
@table_name char(30)
select @table_name = 'temp_a'

select "create table " + @table_name  + " ( " 

select
isnull(c.name, 'NULL') +  "       " +
rtrim(isnull(convert(char(30), x.xtname),isnull(convert(char(30),get_xtypename(c.xtype, c.xdbid)),t.name))) +
"(" + convert(varchar,c.length)  + ")"   +
case when convert(bit, (c.status & 8)) = 0 then " NOT NULL" else " NULL"  end  +
","
from syscolumns c, systypes t, sysxtypes x
where c.id = object_id(@table_name)
and c.usertype *= t.usertype
and c.xtype *= x.xtid
 

 ************* THIS IS NOT COMPLETE ******** you still need to handle identities, precision for data types ,
default values and indexes

Author

Commented:
Thanks Maneksh,
I'll go through this but for sure there is a script out there that extract the database structure!
For sure many of you guys faced an issue like this before, can anyone send a prepared script to me as I think preparing one by myself will take a long time.

Regards,
Ramez Nabil
Joe WoodhousePrincipal Consultant
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
Google db_schema.pl. It's a Perl script (i.e. third-party, not written or supported by Sybase) that gives you a lot of options and control. And yes, you can tell it among other things to extract the entire database structure, either into one output file, one file per object type, or one file per object.

You'll need Perl and SybPerl installed to make this work. It's available at:

   http://www.peppler.org/downloads/dbschema-2_4_2.zip

Another option is Sybase's PowerDesigner. You can download a free eval version, and it has the ability to reverse-engineer databases for you. Again you have a lot of control over what it does. It's available at:

   http://eshop.sybase.com/eshop/buycontent?id=17860&title=PowerDesigner%2016.0%20for%20Windows%20x86%20Eval&location=

If you only need to do this as a one-off scenario I'd say go with the PowerDesigner eval. If this is an ongoing requirement, go with db_schema.pl (or buy PowerDesigner :) ).
Sybase DBA
CERTIFIED EXPERT
Commented:
I think another good option is ddlgen. You can write  a shell script to call ddlgen to generate either all certain type of objects or individual objects in a database. It is very flexible and provides reliable scripts. It is part of ASE and supported by Sybase. You can find all details in utility guide of your version:

http://infocenter.sybase.com/help/index.jsp

Author

Commented:
Thanks a lot alpmoon, the ddlgen is exactly what i was looking for :)
It's realy amazing, very simple and very fast. I just did the following to have it run:
- Install JRE 1.1.8
- Put these paths in the path environment variable: C:\sybase\ASEP\bin;C:\sybase\ASEP\lib;

I now have all database objects in DDL statements on files :)

Thanks also Joe :) ,, I started working on the Perl script and then got an error related to DBlib.pm, while investigating in this error, alpmoon saved me & my time by "ddlgen" :)

Thanks a lot guys,
Ramez Nabil