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.