sql server:
select c.name as columnname
,(select ep.[value]
from sys.extended_properties as ep with(nolock) where ep.major_id=c.[object_id] and ep.minor_id=c.column_id)
as [description]
,(select tp.name from sys.types as tp with(nolock) where tp.user_type_id=c.user_type_id) as columntype
, c.max_length/case when (select tp.name from sys.types as tp with(nolock) where tp.user_type_id=c.user_type_id) in ('nchar','nvarchar','ntext') then 2 else 1 end as [columnlength]
, c.is_nullable as [isenablenull]
, c.is_identity as [isidentity]
, c.is_computed as [iscalculate]
, case when exists(
select 1 from sys.indexes as i with(nolock) inner join sys.index_columns as ic with(nolock)
on i.[object_id]=ic.[object_id] and i.index_id=ic.index_id
and ic.[object_id]=c.[object_id] and ic.column_id=c.column_id
where i.is_primary_key=1
) then 1 else 0 end as [istablekey]
, (select cmp.definition
from sys.computed_columns cmp with(nolock) where cmp.[object_id]=c.[object_id] and c.column_id=cmp.column_id)
as [calcuexpress]
from sys.[columns] as c with(nolock)
where c.[object_id]= object_id('tablename')
order by c.column_id
mysql:
select
`column_name` as `columnname`
,`column_comment` as `description`
,`data_type` as `columntype`
, `character_maximum_length` as `columnlength`
, case when `is_nullable`='no' then 1 else 0 end as `isenablenull`
, case when `extra` like '%auto_increment%' then 1 else 0 end as `isidentity`
, case when `extra` like '%virtual generated%' then 1 else 0 end as `iscalculate`
, case when `column_key`='pri' then 1 else 0 end as `istablekey`
,`generation_expression` as `calcuexpress`
from information_schema.columns
where table_schema = database()
and table_name = 'tablename'
sqlite:
pragma table_info('tablename')