Introspecting DEFAULT values and computed columns

Question

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.

score 6 · Answer 1 · answered Jan 31 '10 at 14:36

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 :)

gd047 · Accepted Answer · 2010-01-31T09:16:34.967

2

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.

edited Jan 31 '10 at 09:16

answered Jan 31 '10 at 08:59

gd047

29,749
18
107
146

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

ashutosh5111 · Answer 3 · 2020-01-03T06:25:39.703

SELECT o.name tab_nm,
       c.name col_nm,
       (CASE WHEN t.name is null THEN (select st.name from systypes st where st.type = c.type) ELSE t.name END) type,
       cm.text deflt,
       c.length length
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'

Introspecting DEFAULT values and computed columns

3 Answers3