数据库教程: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')