How to see The Data Type Of A Column In Sybase IQ

Today post an example of how to see the type of data that has a column in a Sybase IQ, the code has been tested against a version of Sybase 15.4.

With this statement we can see all the data types of all columns in the tables in the database.

 
SELECT
  t.table_name,
  c.column_name,
  d.domain_name,
  c.width,
  c.scale
FROM SYS.SYSTAB t
JOIN SYS.SYSCOLUMN c
  ON t.table_id = c.table_id
JOIN SYS.SYSDOMAIN d
  ON d.domain_id = c.domain_id
WHERE 
  t.creator <> 0 

The output is similar to this;

table_name     column_name           domain_name    width  scale  
-------------- --------------------- -------------- ------ ------ 
DX_COMAD   DXCAD_ID                  unsigned int   4      0      
DX_COMAD   DXCAD_CODE_COMNXDD        unsigned int   4      0      
DX_COMAD   DXCAD_NAME_COMNXDD        varchar        50     0      
DX_COMAD   DXCAD_NUMBER_ADDR_S       unsigned int   4      0  
....

Look for a specific type (in this case exponential format);

SELECT
  t.table_name,
  c.column_name,
  d.domain_name,
  c.width,
  c.scale
FROM SYS.SYSTAB t
JOIN SYS.SYSCOLUMN c
  ON t.table_id = c.table_id
JOIN SYS.SYSDOMAIN d
  ON d.domain_id = c.domain_id
WHERE 
  t.creator <> 0  AND
  d.domain_name = 'double' 

Look for a specific table;

SELECT
  t.table_name,
  c.column_name,
  d.domain_name,
  c.width,
  c.scale
FROM SYS.SYSTAB t
JOIN SYS.SYSCOLUMN c
  ON t.table_id = c.table_id
JOIN SYS.SYSDOMAIN d
  ON d.domain_id = c.domain_id
WHERE 
  t.creator <> 0  AND
  t.table_name IN ('DW_LICENSE_REPORT')

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s