目录
1.MySQL
1.1查看表注释
用 SHOW TABLE STATUS [FROM db_name]
SHOW TABLE STATUS; --数据库下所有表注释
SHOW TABLE STATUS FROM 'dbname'; --数据库[dbname]下所有表注释
SHOW TABLE STATUS FROM 'dbname' WHERE NAME = 'table'; --查看表[table]的注释
1.2查看字段信息
-- 查询数据库 db_name 的 表 table_name 的字段信息
select * from information_schema.columns T
where table_schema ='db_name' and table_name = 'table_name';
2.Oracle
2.1查看表注释
user_tab_comments
SELECT * FROM user_tab_comments t WHERE t.table_name='TABLE_NAME';
eg:
SELECT * FROM user_tab_comments t WHERE t.table_name='USER_INFO';
2.2查看字段信息
user_col_comments(字段注释) 、user_tab_columns(当前用户,字段其他信息)、all_tab_columns(所有用户)
-- 查询 字段名称、注释
SELECT t.column_name, t.comments
FROM user_col_comments t
WHERE t.table_name='USER_INFO';
-- 查询 字段名称、类型、长度、是否为空
SELECT t.column_name, t.data_type, t.data_length, t.nullable
FROM user_tab_columns t
WHERE t.table_name='USER_INFO';
-- 查询 字段名称、类型、长度、是否为空、注释
SELECT t1.column_name, t1.data_type, t1.data_length, t1.nullable, t.comments
FROM user_col_comments t
LEFT JOIN user_tab_columns t1 ON t1.table_name = t.table_name AND t1.column_name = t.column_name
WHERE t.table_name='USER_INFO';
3.sqlsever 2005
3.1查看字段信息
-- 查询 表名称、字段名称、注释
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
WHERE A.name = 'table_name'