Return list of Column names and any default values and Data Types in Table

[former_SQLA_member1694879]

Is there a way to return a list of Column names, data types and if they have default values what they are?

I can get the Column names like this...

select c.column_name
from systabcol c 
key join systab t on t.table_id=c.table_id 
where t.table_name='tablename'

... but it would be really useful to be able also return the data types and if they have a default value or not

UPDATE

Figured out I can get the Column Name, Type, Nullable and Primary Key with

DESCRIBE TableName

Just need to find a way to return the default value and I've cracked the job!

Thank you

[Breck_Carter · Accepted solution]

SYSTABCOL."default"

[former_SQLA_member1694879 · Accepted solution]

That got me on the correct path - this gives me all I need

select  *
from systabcol key join systab
where table_name = 'Customers';

[former_SQLA_member1694879 · Accepted solution]

What is max_identity? It seems to appear for autoincrement primary key columns (e.g. 44) whilst others are zero....

[VolkerBarth · Accepted solution]

Yes, that's for (global) autoincrement defaults - which need not to be primary keys. Cf. that FAQ...