根据数据库表结构写字典文档时使用的sql语句

1, mysql查询表结构的SQL语句如下：

SELECT   
t.TABLE_NAME AS '表名',
t.COLUMN_NAME AS '列名',
t.COLUMN_TYPE AS '数据类型',
CASE
    WHEN IFNULL( t.COLUMN_KEY, ' ' ) = 'PRI' THEN '是' 
    ELSE '否'
END AS '是否主键',
CASE
    WHEN IFNULL( t.COLUMN_DEFAULT, ' ' ) = '' THEN ' ' 
    WHEN t.COLUMN_DEFAULT = 'Null' THEN 'NULL' 
    ELSE t.COLUMN_DEFAULT 
END AS '默认值',
CASE
    WHEN t.IS_NULLABLE = 'YES' THEN '是' 
    ELSE '否' 
END AS '是否允许为空',
t.COLUMN_COMMENT AS '字段说明' 
FROM
  information_schema.COLUMNS t 
WHERE
  t.TABLE_SCHEMA = 'wangxiao_shelves' 
  AND t.TABLE_NAME = 'wx_trade_order';
 -- t.TABLE_SCHEMA  '数据库名'
 -- t.TABLE_NAME  '数据中的表名'
 

2，sqlServer查询表结构的SQL语句如下：

SELECT   
    c.TABLE_NAME AS '表名',
    c.COLUMN_NAME AS '列名',
    c.DATA_TYPE AS '数据类型',
    CASE
        WHEN ic.COLUMN_NAME IS NOT NULL THEN '是' 
        ELSE '否'
    END AS '是否主键',
    CASE
        WHEN c.COLUMN_DEFAULT IS NULL THEN ' ' 
        ELSE c.COLUMN_DEFAULT 
    END AS '默认值',
    CASE
        WHEN c.IS_NULLABLE = 'YES' THEN '是' 
        ELSE '否' 
    END AS '是否允许为空',
    ep.value AS '字段说明' 
FROM
    INFORMATION_SCHEMA.COLUMNS c
    LEFT JOIN (
        SELECT COLUMN_NAME, TABLE_NAME
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
    ) ic ON c.TABLE_NAME = ic.TABLE_NAME AND c.COLUMN_NAME = ic.COLUMN_NAME
    LEFT JOIN sys.extended_properties ep ON ep.major_id = OBJECT_ID(c.TABLE_NAME) AND ep.minor_id = COLUMNPROPERTY(OBJECT_ID(c.TABLE_NAME), c.COLUMN_NAME, 'ColumnId')
WHERE
    c.TABLE_SCHEMA = 'dbo.库名'  -- Replace 'dbo' with your database schema
    AND c.TABLE_NAME = 'dbo.表名'