Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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".

Answer 5

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 :)

Answer 6

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%'

Answer 7

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.