sqlsqlanywheresystem-tables

SQL Anywhere query to dig schema structure

I'm trying to make a SQL query which does the following things:

  1. Select all tables in a schema
  2. Select all columns from the table
  3. Select the columns type (char, int, tinyint, etc)
  4. Select the columns index type or NULL (index type being FK or PK)
  5. If there is a foreign key index, select the primary table

This data should then be put in a XML file. Please note that this is in SQL Anywhere.

The code for the XML file is easily written, but I'm stuck at the query.

I currently have a query that can select all columns, their table and their data type.

SELECT t.table_name AS table_name, 
       c.column_name AS column_name, 
       c.base_type_str 
FROM sys.systabcol c 
    INNER JOIN sys.systab t 
        ON t.table_id = c.table_id 
WHERE t.table_type_str = 'BASE' 
    AND t.table_name NOT LIKE 'ISYS%';

which currently returns for example:

[6585]=>
    array(3) {
      ["table_name"]=>
      string(17) "my_table"
      ["column_name"]=>
      string(6) "number"
      ["base_type_str"]=>
      string(7) "integer"
    }

My desired result would be (for example):

[6585]=>
    array(3) {
      ["table_name"]=>
      string(17) "my_table"
      ["column_name"]=>
      string(6) "number"
      ["base_type_str"]=>
      string(7) "integer" // or other types
      ["index_type"]=>
      string(7) "FK" // or "PK" or "NULL"
      ["primary_table"]=>
      string(7) "some_other_table" // or "NULL"
    }

I'm aware SQL Anywhere has system tables like: SYSFKEY, SYSIDX and SYSIDXCOL, but I have no idea how I'd implement this into my own query.

I've looked around on the internet, and I can find a lot of examples for other SQL services, but none for SQL Anywhere.

I could really use some assistance on this.

Update 1:
So I've figured out that the table SYSFKEY has some interesting columns.

where primary_table_id seems to refer to the foreign table.

Also the table SYSIDX has the columns

I hope I can somehow connect all of this together

Update 2: So I've written a new query, which does suprisingly good actually. I thought I fixed it, until I ran into some oddities.

The query is currently this:

SELECT tab.table_name as table_name,
       col.column_name as column_name,
       col.`default` as default_value,
       col.base_type_str,
       (
           case idx.index_category
           when 1 then 'PK'
           when 2 then 'FK'
           else 'NULL'
           end
       ) as index_type,
       tab1.table_name as foreign_table
FROM sys.systabcol col
    INNER JOIN sys.systab tab
        ON tab.table_id = col.table_id
    LEFT JOIN sys.sysidx idx
        ON idx.table_id = col.table_id
    LEFT JOIN sys.sysidxcol idxc
        ON idxc.table_id = idx.table_id AND idxc.index_id = idx.index_id
    LEFT JOIN sys.sysfkey fk
        ON fk.foreign_table_id = idx.table_id AND fk.foreign_index_id = idx.index_id
    LEFT JOIN sys.systab tab1
        ON tab1.table_id = fk.primary_table_id
WHERE tab.table_name LIKE 'tab%' OR tab.table_name LIKE 'vw%';

And this actually returns a lot of useful information! But something weird is happening.

There are multiple primary keys in a table. Perhaps thats just how the creators designed it to be, that every field together is the primary key, but it seems odd to me.

There are also multiple duplicates (multiple rows with columns that have been there before)

Example with multiple PK: 

        table, th, td {
            border: 1px solid black;
            border-collapse: collapse;
        }
    
<table>
    <thead>
    <tr>
        <th colspan="4">tabMobilinkTabellen</th>
    </tr>
    </thead>
    <tbody>
    <tr><th>Key</th><th>Column</th><th>Type</th><th>Default</th></tr><tr><td>PK</td><td>MltGid</td><td>integer</td><td>autoincrement</td></tr><tr><td>PK</td><td>MltLastModified</td><td>timestamp</td><td>timestamp</td></tr><tr><td>PK</td><td>MltTablename</td><td>nchar(128)</td><td>''</td></tr>    </tbody>
</table>

Example with multiple indexes:

        table, th, td {
            border: 1px solid black;
            border-collapse: collapse;
        }
    
<table>
    <thead>
    <tr>
        <th colspan="4">tabAanhef</th>
    </tr>
    </thead>
    <tbody>
    <tr><th>Key</th><th>Column</th><th>Type</th><th>Default</th></tr><tr><td><a href="?table=tabTaal">FK</a></td><td>AanhefGid</td><td>integer</td><td>autoincrement</td></tr><tr><td></td><td>AanhefGid</td><td>integer</td><td>autoincrement</td></tr><tr><td></td><td>AanhefGid</td><td>integer</td><td>autoincrement</td></tr><tr><td>PK</td><td>AanhefGid</td><td>integer</td><td>autoincrement</td></tr><tr><td><a href="?table=tabTaal">FK</a></td><td>AanhefTaalGid</td><td>integer</td><td></td></tr><tr><td></td><td>AanhefTaalGid</td><td>integer</td><td></td></tr><tr><td></td><td>AanhefTaalGid</td><td>integer</td><td></td></tr><tr><td>PK</td><td>AanhefTaalGid</td><td>integer</td><td></td></tr><tr><td><a href="?table=tabTaal">FK</a></td><td>GeslachtAanhef</td><td>smallint</td><td>0</td></tr><tr><td></td><td>GeslachtAanhef</td><td>smallint</td><td>0</td></tr><tr><td></td><td>GeslachtAanhef</td><td>smallint</td><td>0</td></tr><tr><td>PK</td><td>GeslachtAanhef</td><td>smallint</td><td>0</td></tr><tr><td><a href="?table=tabTaal">FK</a></td><td>TekstAanhef</td><td>nchar(40)</td><td>''</td></tr><tr><td></td><td>TekstAanhef</td><td>nchar(40)</td><td>''</td></tr><tr><td></td><td>TekstAanhef</td><td>nchar(40)</td><td>''</td></tr><tr><td>PK</td><td>TekstAanhef</td><td>nchar(40)</td><td>''</td></tr><tr><td><a href="?table=tabTaal">FK</a></td><td>TekstAdres</td><td>nchar(40)</td><td>''</td></tr><tr><td></td><td>TekstAdres</td><td>nchar(40)</td><td>''</td></tr><tr><td></td><td>TekstAdres</td><td>nchar(40)</td><td>''</td></tr><tr><td>PK</td><td>TekstAdres</td><td>nchar(40)</td><td>''</td></tr><tr><td><a href="?table=tabTaal">FK</a></td><td>TekstBrief</td><td>nchar(40)</td><td>''</td></tr><tr><td></td><td>TekstBrief</td><td>nchar(40)</td><td>''</td></tr><tr><td></td><td>TekstBrief</td><td>nchar(40)</td><td>''</td></tr><tr><td>PK</td><td>TekstBrief</td><td>nchar(40)</td><td>''</td></tr>    </tbody>
</table>

Perhaps I should merge them together, but then still its odd there are multiple FK indexes on one column, right? Or perhaps the query gets stuck and just keeps dumping the same information in every result?

Solution

  • So it took me a lot of time, but I've finally figured out a way to do it. I'm sharing this answer in case anyone else ever runs into this.

    The answer isn't perfect, and will still produce some duplicate rows, even with the LIST() function. This only becomes a problem when there is an intermediate table. I'm still trying to figure this one out, but most of the problems can be filtered out with simple PHP (or your preferred language).

    My final query for now is:

    SELECT tab.table_name as table_name,
       col.column_name as column_name,
       col.`default` as default_value,
       col.base_type_str,
       LIST(
           case idx.index_category
           when 1 then 'PK'
           when 2 then 'FK'
           end
       ) as index_type,
       tab1.table_name as foreign_table
FROM sys.systabcol col
    LEFT JOIN sys.sysidxcol idxc
        ON idxc.table_id = col.table_id AND idxc.column_id = col.column_id
    INNER JOIN sys.systab tab
        ON col.table_id = tab.table_id
    LEFT JOIN sys.sysidx idx
        ON idx.table_id = idxc.table_id AND idx.index_id = idxc.index_id
    LEFT JOIN sys.sysfkey fk
        ON fk.foreign_table_id = idx.table_id AND fk.foreign_index_id = idx.index_id
    LEFT JOIN sys.systab tab1
        ON tab1.table_id = fk.primary_table_id
WHERE tab.table_name LIKE 'tab%' OR tab.table_name LIKE 'vw%' GROUP BY tab.table_name, col.column_name, col.`default`, col.base_type_str, tab1.table_name ORDER BY index_type DESC;

    This query will:

    The where clause is really specific to my database schema, as it filters to all tab% (tables) and vw% (views), but this can be anything. Although when not specifying this will give you all system tables as well.