sqlserver和mysql查询表的字段和注释内容

sqlserver：

一、查询表注释

select top 1000
ROW_NUMBER() OVER (ORDER BY a.name) AS No,
a.name AS 表名,
CONVERT(NVARCHAR(100),isnull(g.[value],'-')) AS 说明
from
sys.tables a left join sys.extended_properties g
on (a.object_id = g.major_id AND g.minor_id = 0)

二、查询表的字段和字段注释

SELECT
　　A.name AS table_name,
　　B.name AS column_name,
　　C.value AS column_description
　　FROM sys.tables A
　　INNER JOIN sys.columns B ON B.object_id = A.object_id
　　LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id

三、获取(表 AA_Cus)表字段和字段类型

select column_name as name,data_type as type
from information_schema.columns
where table_name = 'AA_Cus'

参考https://blog.51cto.com/cplvfx/5392016

mysql：

一、查询所有表的注释

SELECT
table_name 表名,
table_comment 表说明
FROM
information_schema.TABLES
WHERE
table_schema = 'ry-vue-wms' #此处填写数据库名称
ORDER BY
table_name

二、查询所有表及字段的注释

SELECT
a.table_name 表名,
a.table_comment 表说明,
b.COLUMN_NAME 字段名,
b.column_comment 字段说明,
b.column_type 字段类型,
b.column_key 约束
FROM
information_schema. TABLES a
LEFT JOIN information_schema. COLUMNS b ON a.table_name = b.TABLE_NAME
WHERE
a.table_schema = 'ry-vue-wms' #这里填写数据库名称
ORDER BY
a.table_name

三、查询某表的所有字段的注释

select
COLUMN_NAME 字段名,
column_comment 字段说明,
column_type 字段类型,
column_key 约束 from information_schema.columns
where table_schema = 'ry-vue-wms' #这里填写数据库名称
and table_name = 'bas_warehouse' ; #这里填写表的名称