SQL Server、MySQL和Sqlite获取表结构的方法

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