views:

246

answers:

2

For a Perl library that dumps Sybase schemas for DBIx::Class (::Schema::Loader), I need to be able to introspect DEFAULTs and computed columns.

Suppose we have:

create table bar (
  id INTEGER IDENTITY PRIMARY KEY,
  foo VARCHAR(10) DEFAULT 'foo',
  adt AS getdate(),
  ts timestamp
)

Here's as far as I got:

select substring(c.name,1,5) name, c.cdefault, c.computedcol from syscolumns c
join sysobjects o on c.id = o.id where o.name = 'bar' and o.type = 'U'

name       cdefault    computedcol 
---------- ----------- ----------- 
id                   0        NULL 
foo          602182610        NULL 
adt                  0   618182667 
ts                   0        NULL 

This tells me that column 'foo' has a stored procedure with id 602182610 that returns the value. How do I get the original DEFAULT 'foo' from this id?

The timestamp column does not have computed column object nor a default sproc, but I somehow need to know that it is in fact a timestamp column. Looking at the data type returned by DBI for it tells me that it's 'varbinary', the internal representation of a timestamp. How do I know if it is or isn't one?

It also tells me that column 'adt' is a computed column, the object for this column having id 618182667.

Looking in sysobjects for that id tells me little that seems useful except:

select substring(name,1,15) name, type from sysobjects where id = 618182667

name                           type 
------------------------------ ---- 
bar_adt_6181826                C    

Any help much appreciated.

+1  A: 

Regarding your first question, about defaults

select text from syscomments 
where id = 602182610

As for timestamp columns, the type column in syscolumns references systypes.type. In that table name column contains the datatype name.

gd047
Incidentally that works for computed columns too! Thank you very much.
Rafael Kitover
systypes for type = 37 returns both varbinary and timestamp, how do I know if a specific column is varbinary or timestamp?
Rafael Kitover
If I lookup into systypes with both type and usertype, then it lets me distinguish between timestamp and varbinary, cool.
Rafael Kitover
A: 

This is the query I ended up using in case anyone is interested:

SELECT c.name name, t.name type, cm.text deflt
FROM syscolumns c         
JOIN sysobjects o ON c.id = o.id
LEFT JOIN systypes t ON c.type = t.type AND c.usertype = t.usertype
LEFT JOIN syscomments cm  
  ON cm.id = CASE WHEN c.cdefault = 0 THEN c.computedcol ELSE c.cdefault END
WHERE o.name = 'table_name' AND o.type = 'U'

Seems to work well, though I still need to write some more data type tests :)

Rafael Kitover