Is there a way to extract table DDL from any version of SQL Anywhere. For example, SQL Server has an assembly you can call to get pretty much any information from the database, including DDL for DB objects.

Thanks,

Brad

asked 08 Nov '09, 21:58

Brad%20Wery's gravatar image

Brad Wery
382192126
accept rate: 0%


You might want to look at the sa_get_table_definition() system function in 11.0.1. It uses the same gear as dbunload for extracting the definitions from the catalog.

permanent link

answered 21 Dec '09, 17:22

Bruce%20Hay's gravatar image

Bruce Hay
2.6k1510
accept rate: 48%

You can also copy and paste individual database objects from Sybase Central into a text editor (eg notepad) where you get the DDL for that object.

permanent link

answered 09 Nov '09, 18:03

Justin%20Willey's gravatar image

Justin Willey
7.0k115148219
accept rate: 21%

This doesn't work for me. My tool would display DDL to the user based on a database object that they selected.

(11 Nov '09, 17:03) Brad Wery

I too have wondered if SQL Anywhere had some sort of system procedures to extract DDL from any type of database object. Of course, some of these things can be queried by extracting information directly from the system tables, but that feels a bit hacky. SQL Anywhere does support some ASE procedures which get DDL information for procedures, indexes, and views (sp_helptext), but nothing for tables.

As mentioned, you can select any object in Sybase Central and copy the DDL to your clipboard. In your case, this is of no use. With the help of a packet sniffer, I have found that Sybase Central is actually querying the system tables for table/column information and building the DDL itself. Manually building the DDL, or using dbunload, may be your only options.

If you're going to use dbunload, yes, you're right, it's overkill. I would suggest perhaps running dbunload only intermittently and storing the results in memcache or some other temporary location. It's extra maintenance and complexity to your program, but should be sufficient unless the table schema changes very often.

It would be nice to have system procedures that can retrieve the schema of any object, much like the "copy and paste" functionality within Sybase Central.

permanent link

answered 23 Nov '09, 22:38

Ralph%20Wissing's gravatar image

Ralph Wissing
3153711
accept rate: 0%

Brad: Ralph's fresh answer prompted me to take another look and read your comments. No, there isn't any facility like what you want, and yes, it is sorely needed. I am personally embarrassed at how difficult it's been over the years to keep Foxhound's schema display up to date with respect to all changes, and I have profound respect for the author(s) of dbunload. I'm guessing you're trying to do the same thing. Suggestion: Describe exactly what you want, and why (make a business case), and post a new question here with the Title "Product suggestion: etcetera".

permanent link

answered 24 Nov '09, 04:38

Breck%20Carter's gravatar image

Breck Carter
27.0k444614889
accept rate: 21%

If memory serves (it often doesn't) the ANSI spec has something to say on this subject... the ANSI standard is like a baseball bat, most useful when you want to hit something with it.

(24 Nov '09, 04:41) Breck Carter

I've tried to make my own Version Control System inside a database for all db objects and used the following function and view. It's quite old (ASA 8) and not complete (indexes, foreign keys, ...) but it's a start.

create FUNCTION "DBA"."VCS_TableDef"(in tableid integer,in Creator varchar(255),in Name varchar(255),in tbconstr long varchar)
returns long varchar
begin
  declare res long varchar;
  declare pkey integer;
  set res='CREATE TABLE "'+Creator+'"."'+Name+'"'+"char"(13)+"char"(10)+'('+"char"(13)+"char"(10);
  set pkey=0;
  for curs as curs1 scroll cursor for
    select "char"(9)+'"'+column_name+'"'+"char"(9)+isnull((select type_name from sys.sysusertype where type_id = user_type),(select domain_name from sys.sysdomain where domain_id = syscolumn.domain_id))
      +(if user_type is null then(if domain_id in( 11,7,8,9) then '('+string(width)+')' else(if domain_id = 3 then '('+string(width)+','+string(scale)+')'
      endif)
      endif) endif)+(if nulls = 'N' then ' NOT NULL'
      else ' NULL'
      endif)+(if "default" is not null then ' DEFAULT '+"default"+(if "check" is not null then ' check '+"check"
      endif)
      endif) as coldef,
      (select check_defn from sys.syscheck where check_id = (select constraint_id from sys.sysconstraint where table_object_id = tableid and ref_object_id = syscolumn.object_id and constraint_type = 'C')) as "check" from sys.syscolumn where table_id = tableid order by column_id asc do
    if pkey <> 0 then
      set res=res+','+"char"(13)+"char"(10)
    end if;
    set res=res+coldef;
    set pkey=1 end for;
  set pkey=0;
  for curs as curs2 scroll cursor for
    select '"'+column_name+'"' as colnm from sys.syscolumn where table_id = tableid and pkey = 'Y' order by column_id asc do
    if pkey = 0 then
      set res=res+','+"char"(13)+"char"(10)+"char"(9)+'PRIMARY KEY (';
      set pkey=1
    else
      set res=res+', '
    end if;
    set res=res+colnm end for;
  if pkey <> 0 then
    set res=res+')'
  end if;
  set pkey=0;
  for curs as curs3 scroll cursor for
    select '"'+column_name+'"' as colnm from sys.sysindex,sys.sysixcol,sys.syscolumn where sysindex.table_id = tableid and sysindex."unique" = 'U' and sysindex.index_id = sysixcol.index_id and sysixcol.table_id = tableid and syscolumn.table_id = tableid and sysixcol.column_id = syscolumn.column_id order by sysindex.index_id asc do
    if pkey = 0 then
      set res=res+','+"char"(13)+"char"(10)+"char"(9)+'UNIQUE (';
      set pkey=1
    else
      set res=res+', '
    end if;
    set res=res+colnm end for;
  if pkey <> 0 then
    set res=res+')'
  end if;
  if tbconstr is not null then
    set res=res+','+"char"(13)+"char"(10)+"char"(9)+tbconstr
  end if;
  set res=res+"char"(13)+"char"(10)+')';
  return(res)
end;

CREATE VIEW "DBA"."VCS_SourceCode" as
  select sysusers.name as srccreator,
    sysprocedure.proc_name as srcname,
    proc_defn as srcsource,'P' as srcart,
    proc_id as srcid from
    sys.sysprocedure,dbo.sysusers where
    creator = uid union all
  select sysusers.name as srccreator,
    systable.table_name as srcname,
    (if table_type = 'View' then view_def else DBA.VCS_TableDef(table_id,srccreator,srcname,view_def) endif) as srcsource,
    substr(table_type,1,1) as srcart,
    table_id as srcid from
    sys.systable,dbo.sysusers where
    creator = uid union all
  select(select user_name from SYS.SYSUSERPERM where
      user_id = (select SYSTABLE.creator from SYS.systable where systable.table_id = systrigger.table_id)),
    trigger_name as srcname,
    trigger_defn as srcsource,'T' as srcart,
    trigger_id as srcid from
    sys.systrigger where
    foreign_table_id is null;

I also used it to search for specific code segments, before this functionality was available in Sybase Central.

select * from vcs_sourcecode where srcsource like '%inout%'
permanent link

answered 17 Dec '09, 15:18

Markus%20D%C3%BCtting's gravatar image

Markus Dütting
53641220
accept rate: 30%

edited 17 Dec '09, 15:23

The SQL Anywhere dbunload.exe utility can be used to write all the schema to a text file. Use the -n option to do just the schema, not the data. The new -no option sorts the CREATE TABLE statements in alphabetic order.

You can do this from Sybase Central, but I'm a command-line kinda guy so I'll let other people talk about the wizards.

The following blog post talks about using dbunload to answer "Question: How do I compare the schemas of two SQL Anywhere databases?"...

http://sqlanywhere.blogspot.com/2009/10/comparing-database-schemas-improved.html

If dbunload-to-text-file is not what you're looking for, maybe you could refine your question (make it more specific... point to the SQL Server feature(s) you are talking about)... or ask it as a new question, the more questions the better.

And congratulations on posting the second question! (it's really the first question, if you exclude cheaters like the site administrator, i.e., me :)

permanent link

answered 09 Nov '09, 10:02

Breck%20Carter's gravatar image

Breck Carter
27.0k444614889
accept rate: 21%

1

I would need to get the DDL for an individual object. dbunload may work but it may be over kill.

More information: I'm providing users with a list of database objects (e.g. tables, procedures etc.). They have the ability to click on an object and see the DDL for that object. Procedures is easy, I can get the syntax from system tables. I can also get table syntax from system tables but I would have to really know what I'm doing in order to put it all together.

(11 Nov '09, 17:02) Brad Wery

While I can not address this issue directly I can discribe what Microsoft has done. The code to provide all of the catalog information including the kind of informaton the poster is interested in has been blackboxed into a class (dll). This DLL is use by the standard management tools to provide schema information in the graphical interface. On the other hand this DLL can be referenced by a program and so you can build your own interface to the catalog tables. This is very handy, when Microsoft changed the default way a schema file was generated. I was able to find an application on Code Project that did most of what I wanted to have it the way it was before the update(sqlserver 2005). The tool had a nifty feature, table and view definition in are generated in dependency order, something the stock tools from MS will not do. Since DBUNLOAD already has all of the code for reading and extracting the information from the catalog tables if this code could be seperated out and made availabe like the code from MS as a dll it would go a long way toward increasing the number of third party tools available for ASA. Sybase should consider doing the same same for ASE.

permanent link

answered 15 Feb '10, 19:54

Jay%20Turner's gravatar image

Jay Turner
4113
accept rate: 0%

1

Hello Jay. Yes, this is the assembly I was talking about. It makes it so easy to get anything from SQL Server. I was hoping the same thing existed for SQL Anywhere (and ASE for that matter).

Breck, if you packaged your stuff in an assembly... I would license it. :)

(15 Feb '10, 21:15) Brad Wery

@Brad: Still feel free to post your requirements as a product suggestion as Breck has mentioned in one of his answers. That's the best way to take influence on the ASA development:)

(15 Feb '10, 21:35) Volker Barth

@Brad: Yes, post a product suggestion here, AND post it on the product futures newsgroup if you want full exposure... after all, SQLA (this site) is still in Beta. Explain exactly what you want, AND give a business case... if you can, give a business case that extends beyond creating a commercial tool for developers... a nice "RFP Checkpoint" justification, for example :)

(16 Feb '10, 15:08) Breck Carter

There is a simple tool the DDL Extractor which iterates over the entire server, writing ddl scripts to a local directory. http://www.antipodeansoftware.com/Home/Products

permanent link

answered 19 Aug '10, 01:32

uberdarryl's gravatar image

uberdarryl
1
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×28
×9

question asked: 08 Nov '09, 21:58

question was seen: 10,131 times

last updated: 19 Aug '10, 01:32