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