Introspective DEFAULT Values ​​and Computed Columns

Question

For the Perl library, which removes Sybase schemas for DBIx :: Class (:: Schema :: Loader), I need to be able to parse DEFAULT and computed columns.

Let's pretend that:

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

Here, as I understand it:

 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 the column "foo" has a stored procedure with id 602182610 that returns a value. How to get the original DEFAULT 'foo' from this identifier?

The timestamp column does not have a computed column object and sproc by default, but for some reason I need to know that this is actually a timestamp column. Looking at the data type returned by DBI, he tells me that it is "varbinary", an internal representation of the timestamp. How to find out if it is or not?

He also tells me that the "adt" column is a computed column, the object for this column has the identifier 618182667.

A search in sysobjects for this id tells me a bit that seems useful, except:

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

Any help is greatly appreciated.

Answer 1

Regarding your first question, about defaults

 select text from syscomments where id = 602182610 

Regarding timestamp columns, the type column in syscolumn columns refers to systypes.type . In this table, the name column contains the name of the data type.

Answer 2

This is the query that I used in case someone 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, although I still need to write some more data type tests :)