获取数据库的结构

获取oracel所有表结构和注释

SELECT t1.Table_Name AS "表名称",
t3.comments AS "表说明", 
t1.Column_Name AS "字段名称",
t1.Data_Type AS "数据类型",
t1.Data_Length AS "长度",
t1.NullAble AS "是否为空",
t2.Comments AS "字段说明",
t1.Data_Default "默认值"
FROM cols t1 left join user_col_comments t2
on t1.Table_name=t2.Table_name and t1.Column_Name=t2.Column_Name
left join user_tab_comments t3 
on t1.Table_name=t3.Table_name 
WHERE NOT EXISTS ( SELECT t4.Object_Name FROM User_objects t4
WHERE t4.Object_Type='TABLE' 
AND t4.Temporary='Y' 
AND t4.Object_Name=t1.Table_Name )
--AND t1.Table_Name='表名'
ORDER BY t1.Table_Name, t1.Column_ID;

获取单个表的结构和注释

SELECT T1.TABLE_NAME,
       T1.COLUMN_NAME,
       T1.DATA_TYPE || '(' || T1.DATA_LENGTH || ')',
       T2.COMMENTS
  FROM USER_TAB_COLS T1, USER_COL_COMMENTS T2
 WHERE T1.TABLE_NAME = T2.TABLE_NAME
   AND T1.COLUMN_NAME = T2.COLUMN_NAME
   AND T1.TABLE_NAME = '表名';

分页显示数据

SELECT * FROM (SELECT "NAVICAT_TABLE".*, ROWNUM "NAVICAT_ROWNUM" 
FROM ( SELECT "数据库"."表名".*, ROWID "NAVICAT_ROWID" 
FROM "数据库"."表名") "NAVICAT_TABLE" 
WHERE ROWNUM <= 8000) WHERE "NAVICAT_ROWNUM" > 7000

 获取mysql的数据解释

SELECT
a.TABLE_NAME 表名EN,
b.table_comment as 表名,
a.COLUMN_NAME 列名,
a.COLUMN_TYPE 数据结构,
a.COLUMN_COMMENT 备注
FROM
INFORMATION_SCHEMA.COLUMNS a
LEFT JOIN information_schema.tables b on a.TABLE_NAME = b.table_name
WHERE a.table_schema = 'policydatabase'
order by a.TABLE_NAME

 

posted @ 2021-10-13 10:31  世人皆萌  阅读(72)  评论(0编辑  收藏  举报