Sybase Systypes relationship between Syscolumns, why is it many to one?-CodePudding

I've been trying to capture the datatypes of particular columns and noticed that you must use a join on the system tables to capture it. It doesn't seem to match up with what I'm seeing in Aqua Studio on occasion as well. For instance, the datatype for insert_user is char(8), however, that is producing 3 rows nvarchar, sysname, and varchar, which don't align what the table properties is showing.

Thing 1: Why are there 3 columns per type row? Including usertype isn't a solution since that would just drop data that you would want to see since other columns may not have usertypes which align directly with the systypes entries. For instance, the update_user has a usertype id of 1, however, the types table has 25, 18 and 2 (which is why on the join you see 3 records).

Thing 2: Why does the datatype not match the actual table properties? Is this the wrong table to be using or is there more data that needs to be brought into the join?

Thing 3: Thanks!

Here is the SQL:

select a.id, a.name as table_name, b.*,c.*
from dbo.sysobjects a 
inner join dbo.syscolumns b on (a.id = b.id)
inner join dbo.systypes c on (b.type = c.type)
where a.type = 'U' and a.name = 'mytable'

Here is the list of datatypes the actual table displays:

mytable_id       numeric(18,0)
another_id       numeric(18,0)
artifact_desc    varchar(60)
artifact_active  char(1)
insert_user      char(8)
insert_dt        datetime
update_user      char(8)
update_dt        datetime

Here is what the joins look like when displaying all possible columns. Sorry for the link, this is annoying as a new poster....

https://i.imgur.com/guOjgtg.jpg

CodePudding user response：

Thanks markp-fuso! Here is what I tried and was able to get the correct datatype pulled in. It's no longer cross-joining, so looks like the dbo.systypes I've seen used in other posts was incorrect.

   select a.id, a.name as table_name, b.*,c.local_type_name
   from dbo.sysobjects a 
   inner join dbo.syscolumns b on (a.id = b.id)
   inner join sybsystemprocs.dbo.spt_datatype_info c on (b.type = c.ss_dtype)
   where a.type = 'U' and a.name = 'mytable'

CodePudding user response：

I'm not a sybase user... but the structure is extremely similar to what I see in the RDBMS I use (SQL Server).

You need to join on the column usertype to get the proper datatype

usertype → the datatype you use
type → internal data type the system uses

how the two are related can/should be found in the docs. Other data, like length or precision are stored in the syscolumns table itself.

You just need to look at the docs for syscolumns and systypes (here). the docs of syscolumns explicitly says that usertype is unique, therefore is the one you want to use unless you are trying to get the whole "hierarchy" of datatypes... which is not the case

Therefore your query should be:

select a.id, a.name as table_name, b.*,c.*
from dbo.sysobjects a 
inner join dbo.syscolumns b on (a.id = b.id)
inner join dbo.systypes c on (b.usertype = c.usertype)
where a.type = 'U' and a.name = 'mytable'