SELECT D.OWNER, D.TABLE_NAME, D.COMMENTS
  FROM DBA_TAB_COMMENTS D
 WHERE D.OWNER = 'LHR' 
 AND D.COMMENTS IS NOT NULL;

SELECT
    table_name,
    comments AS table_comment
FROM
    all_tab_comments
WHERE
    table_name = 'your_table_name' -- 替换为你的表名称
    AND owner = 'your_schema_name'; -- 替换为你的模式名称

SELECT D.OWNER, D.TABLE_NAME, D.COMMENTS

FROM DBA_TAB_COMMENTS D

WHERE D.OWNER = 'LHR'

AND D.COMMENTS IS NOT NULL;

SELECT

table_name,

comments AS table_comment

FROM

all_tab_comments

WHERE

table_name = 'your_table_name' -- 替换为你的表名称

AND owner = 'your_schema_name'; -- 替换为你的模式名称

查询列的注释：


SELECT D.OWNER, D.TABLE_NAME,D.COLUMN_NAME, D.COMMENTS
  FROM DBA_COL_COMMENTS D
 WHERE D.OWNER = 'LHR' 
 AND D.TABLE_NAME='CMMND_INFO_HSTRY'
 AND D.COMMENTS IS NOT NULL;

SELECT
    table_name,
    column_name,
    comments AS column_comment
FROM
    all_col_comments
WHERE
    table_name = 'your_table_name' -- 替换为你的表名称
    AND owner = 'your_schema_name'; -- 替换为你的模式名称

SELECT D.OWNER, D.TABLE_NAME,D.COLUMN_NAME, D.COMMENTS

FROM DBA_COL_COMMENTS D

WHERE D.OWNER = 'LHR'

AND D.TABLE_NAME='CMMND_INFO_HSTRY'

AND D.COMMENTS IS NOT NULL;

SELECT

table_name,

column_name,

comments AS column_comment

FROM

all_col_comments

WHERE

table_name = 'your_table_name' -- 替换为你的表名称

AND owner = 'your_schema_name'; -- 替换为你的模式名称

请确保将 "your_database_name"、"your_table_name"、"your_schema_name" 替换为你要查询的数据库、表和模式的实际名称。这些查询语句针对不同的数据库管理系统，可以帮助你查询表和列的注释信息。

示例

CREATE TABLE employees (
    employee_id   NUMBER,
    first_name    VARCHAR2(50),
    last_name     VARCHAR2(50)
);

COMMENT ON TABLE employees IS '员工信息表';

COMMENT ON COLUMN employees.employee_id IS '员工ID';
COMMENT ON COLUMN employees.first_name IS '员工名字';
COMMENT ON COLUMN employees.last_name IS '员工姓氏';

CREATE TABLE employees (

employee_id NUMBER,

first_name VARCHAR2(50),

last_name VARCHAR2(50)

);

COMMENT ON TABLE employees IS '员工信息表';

COMMENT ON COLUMN employees.employee_id IS '员工ID';

COMMENT ON COLUMN employees.first_name IS '员工名字';

COMMENT ON COLUMN employees.last_name IS '员工姓氏';

MySQL

对于MySQL和MariaDB：查询表的注释：

SELECT
    TABLE_NAME,
    TABLE_COMMENT
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_SCHEMA = 'your_database_name' -- 替换为你的数据库名称
    AND TABLE_NAME = 'your_table_name'; -- 替换为你的表名称

SELECT

TABLE_NAME,

TABLE_COMMENT

FROM

INFORMATION_SCHEMA.TABLES

WHERE

TABLE_SCHEMA = 'your_database_name' -- 替换为你的数据库名称

AND TABLE_NAME = 'your_table_name'; -- 替换为你的表名称

查询列的注释：

SELECT
    TABLE_NAME,
    COLUMN_NAME,
    COLUMN_COMMENT
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_SCHEMA = 'your_database_name' -- 替换为你的数据库名称
    AND TABLE_NAME = 'your_table_name'; -- 替换为你的表名称

SELECT

TABLE_NAME,

COLUMN_NAME,

COLUMN_COMMENT

FROM

INFORMATION_SCHEMA.COLUMNS

WHERE

TABLE_SCHEMA = 'your_database_name' -- 替换为你的数据库名称

AND TABLE_NAME = 'your_table_name'; -- 替换为你的表名称

示例

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
  name VARCHAR(50) COMMENT '姓名',
  age INT COMMENT '年龄',
  email VARCHAR(100) COMMENT '电子邮件'
) COMMENT '用户信息表';

SHOW CREATE TABLE users;

SELECT
    TABLE_NAME,
    TABLE_COMMENT
FROM
    INFORMATION_SCHEMA.TABLES
WHERE table_name = 'users'
AND table_schema = 'lhrdb';

SELECT column_name, column_comment
FROM information_schema.columns
WHERE table_name = 'users'
AND table_schema = 'lhrdb';

CREATE TABLE users (

id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',

name VARCHAR(50) COMMENT '姓名',

age INT COMMENT '年龄',

email VARCHAR(100) COMMENT '电子邮件'

) COMMENT '用户信息表';

SHOW CREATE TABLE users;

SELECT

TABLE_NAME,

TABLE_COMMENT

FROM

INFORMATION_SCHEMA.TABLES

WHERE table_name = 'users'

AND table_schema = 'lhrdb';

SELECT column_name, column_comment

FROM information_schema.columns

WHERE table_name = 'users'

AND table_schema = 'lhrdb';

SQL Server


SELECT
表名=case   when   a.colorder=1   then   d.name   else   ''   end,
表说明=case   when   a.colorder=1   then   isnull(f.value,'')   else   ''   end,
字段序号=a.colorder,
字段名=a.name,
标识=case   when   COLUMNPROPERTY(   a.id,a.name,'IsIdentity')=1   then   '√'else   ''   end,
主键=case   when   exists(SELECT   1   FROM   sysobjects   where   xtype='PK'   and   name   in   (
SELECT   name   FROM   sysindexes   WHERE   indid   in(
SELECT   indid   FROM   sysindexkeys   WHERE   id   =   a.id   AND   colid=a.colid
)))   then   '√'   else   ''   end,
类型=b.name,
占用字节数=a.length,
长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空=case   when   a.isnullable=1   then   '√'else   ''   end,
默认值=isnull(e.text,''),
字段说明=isnull(g.[value],'')
FROM   syscolumns   a
left   join   systypes   b   on   a.xusertype=b.xusertype
inner   join   sysobjects   d   on   a.id=d.id     and   d.xtype='U'   and     d.name<>'dtproperties'
left   join   syscomments   e   on   a.cdefault=e.id
left   join   sys.extended_properties   g   on   a.id=g.major_id   and   a.colid=g.minor_id
left   join   sys.extended_properties   f   on   d.id=f.major_id   and   f.minor_id=0
--where   d.name='orders'         --如果只查询指定表,加上此条件
order   by   a.id,a.colorder

SELECT

表名=case when a.colorder=1 then d.name else '' end,

表说明=case when a.colorder=1 then isnull(f.value,'') else '' end,

字段序号=a.colorder,

字段名=a.name,

标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,

主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (

SELECT name FROM sysindexes WHERE indid in(

SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid

))) then '√' else '' end,

类型=b.name,

占用字节数=a.length,

长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),

小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),

允许空=case when a.isnullable=1 then '√'else '' end,

默认值=isnull(e.text,''),

字段说明=isnull(g.[value],'')

FROM syscolumns a

left join systypes b on a.xusertype=b.xusertype

inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'

left join syscomments e on a.cdefault=e.id

left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id

left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0

--where d.name='orders' --如果只查询指定表,加上此条件

order by a.id,a.colorder

查询表的注释：

SELECT
    t.name AS TABLE_NAME,
    ep.value AS TABLE_COMMENT
FROM
    sys.tables t
LEFT JOIN
    sys.extended_properties ep ON ep.major_id = t.object_id AND ep.minor_id = 0 AND ep.class = 1
WHERE
    t.name = 'your_table_name'; -- 替换为你的表名称

SELECT

t.name AS TABLE_NAME,

ep.value AS TABLE_COMMENT

FROM

sys.tables t

LEFT JOIN

sys.extended_properties ep ON ep.major_id = t.object_id AND ep.minor_id = 0 AND ep.class = 1

WHERE

t.name = 'your_table_name'; -- 替换为你的表名称

查询列的注释：

SELECT
    t.name AS TABLE_NAME,
    c.name AS COLUMN_NAME,
    ep.value AS COLUMN_COMMENT
FROM
    sys.tables t
INNER JOIN
    sys.columns c ON t.object_id = c.object_id
LEFT JOIN
    sys.extended_properties ep ON ep.major_id = t.object_id AND ep.minor_id = c.column_id AND ep.class = 1
WHERE
    t.name = 'your_table_name'; -- 替换为你的表名称

SELECT

t.name AS TABLE_NAME,

c.name AS COLUMN_NAME,

ep.value AS COLUMN_COMMENT

FROM

sys.tables t

INNER JOIN

sys.columns c ON t.object_id = c.object_id

LEFT JOIN

sys.extended_properties ep ON ep.major_id = t.object_id AND ep.minor_id = c.column_id AND ep.class = 1

WHERE

t.name = 'your_table_name'; -- 替换为你的表名称

示例

-- 创建示例表
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50)
);

-- 添加表注释
EXEC sys.sp_addextendedproperty
    @name = N'MS_Description',
    @value = N'This table stores employee information.',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'Employees';

-- 添加列注释
EXEC sys.sp_addextendedproperty
    @name = N'MS_Description',
    @value = N'Unique identifier for each employee.',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'Employees',
    @level2type = N'COLUMN',
    @level2name = N'EmployeeID';

EXEC sys.sp_addextendedproperty
    @name = N'MS_Description',
    @value = N'First name of the employee.',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'Employees',
    @level2type = N'COLUMN',
    @level2name = N'FirstName';

EXEC sys.sp_addextendedproperty
    @name = N'MS_Description',
    @value = N'Last name of the employee.',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'Employees',
    @level2type = N'COLUMN',
    @level2name = N'LastName';

EXEC sys.sp_addextendedproperty
    @name = N'MS_Description',
    @value = N'Department where the employee works.',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'Employees',
    @level2type = N'COLUMN',
    @level2name = N'Department';

-- 查询表注释
SELECT
    [ep].[value] AS [TableDescription]
FROM
    sys.tables AS [t]
    INNER JOIN sys.extended_properties AS [ep] ON [ep].[major_id] = [t].[object_id]
WHERE
    [ep].[minor_id] = 0
    AND [ep].[class] = 1
    AND [t].[name] = 'Employees';

-- 查询列注释
SELECT
    [c].[name] AS [ColumnName],
    [ep].[value] AS [ColumnDescription]
FROM
    sys.columns AS [c]
    INNER JOIN sys.extended_properties AS [ep] ON [ep].[major_id] = [c].[object_id] AND [ep].[minor_id] = [c].[column_id]
    INNER JOIN sys.tables AS [t] ON [t].[object_id] = [c].[object_id]
WHERE
    [ep].[class] = 1
    AND [t].[name] = 'Employees';

-- 创建示例表

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,

FirstName VARCHAR(50),

LastName VARCHAR(50),

Department VARCHAR(50)

);

-- 添加表注释

EXEC sys.sp_addextendedproperty

@name = N'MS_Description',

@value = N'This table stores employee information.',

@level0type = N'SCHEMA',

@level0name = N'dbo',

@level1type = N'TABLE',

@level1name = N'Employees';

-- 添加列注释

EXEC sys.sp_addextendedproperty

@name = N'MS_Description',

@value = N'Unique identifier for each employee.',

@level0type = N'SCHEMA',

@level0name = N'dbo',

@level1type = N'TABLE',

@level1name = N'Employees',

@level2type = N'COLUMN',

@level2name = N'EmployeeID';

EXEC sys.sp_addextendedproperty

@name = N'MS_Description',

@value = N'First name of the employee.',

@level0type = N'SCHEMA',

@level0name = N'dbo',

@level1type = N'TABLE',

@level1name = N'Employees',

@level2type = N'COLUMN',

@level2name = N'FirstName';

EXEC sys.sp_addextendedproperty

@name = N'MS_Description',

@value = N'Last name of the employee.',

@level0type = N'SCHEMA',

@level0name = N'dbo',

@level1type = N'TABLE',

@level1name = N'Employees',

@level2type = N'COLUMN',

@level2name = N'LastName';

EXEC sys.sp_addextendedproperty

@name = N'MS_Description',

@value = N'Department where the employee works.',

@level0type = N'SCHEMA',

@level0name = N'dbo',

@level1type = N'TABLE',

@level1name = N'Employees',

@level2type = N'COLUMN',

@level2name = N'Department';

-- 查询表注释

SELECT

[ep].[value] AS [TableDescription]

FROM

sys.tables AS [t]

INNER JOIN sys.extended_properties AS [ep] ON [ep].[major_id] = [t].[object_id]

WHERE

[ep].[minor_id] = 0

AND [ep].[class] = 1

AND [t].[name] = 'Employees';

-- 查询列注释

SELECT

[c].[name] AS [ColumnName],

[ep].[value] AS [ColumnDescription]

FROM

sys.columns AS [c]

INNER JOIN sys.extended_properties AS [ep] ON [ep].[major_id] = [c].[object_id] AND [ep].[minor_id] = [c].[column_id]

INNER JOIN sys.tables AS [t] ON [t].[object_id] = [c].[object_id]

WHERE

[ep].[class] = 1

AND [t].[name] = 'Employees';

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！

后续精彩内容已被小麦苗无情隐藏，请输入验证码解锁本站所有文章

请关注本站微信公众号，回复“小麦苗博客”，获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。

打赏赞(4)

标签： GreenPlum MSSQL MySQL Oracle PG PostgreSQL SQL Server 注释

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

2023年 9月
一	二	三	四	五	六	日
	1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30

AI (2)
ClickHouse (6)
DM达梦 (9)
Docker (46)
GBase (37)
Greenplum (109)
HANA (2)
K8S (14)
Kafka (3)
Memcached (2)
MongoDB (5)
MySQL (211)
NoSQL (3)
OceanBase (14)
OGG (43)
openGauss (11)
Oracle (635)
OS (383)
PostgreSQL (259)
Python (26)
Redis (21)
SQL Server (157)
SQLite (1)
StarRocks (21)
TDSQL (5)
TiDB (18)
YMatrix (5)
中间件 (10)
云 (10)
人大金仓KingBase (9)
其它 (83)
培训考证 (45)
大数据 (6)
存储 (8)
巡检监控 (47)
工具 (100)
数据迁移 (16)
架构篇 (12)
生活 (10)
电脑 (18)
网络 (94)
育儿 (5)
视频 (17)
面试笔试 (38)

嘿，我是小麦，需要帮助随时找我哦

18509239930
个人微信
麦老师QQ聊天
个人邮箱
点击加入QQ群
个人微店
回到顶部

小麦苗

所有数据库查询表和列的注释SQL汇总

Oracle

示例

MySQL

示例

SQL Server

示例

相关文章

您可能还喜欢...

发表回复取消回复

博客寄语

博客概况

本站其它工具

搜标题

搜索本网站

标签云☁

登陆本博客

博客日历

博客归档

博客分类

所有数据库查询表和列的注释SQL汇总

Oracle

示例

MySQL

示例

SQL Server

示例

相关文章

您可能还喜欢...

EMCC添加被监控主机和数据库

个人微店

Oracle查询数据文件（含临时和Undo文件）详情

发表回复 取消回复

博客寄语

博客概况

本站其它工具

搜标题

搜索本网站

标签云☁

登陆本博客

博客日历

博客归档

博客分类

发表回复取消回复