mysql:
查询所有表名,表注释
SELECT
a.table_name 表名,
a.table_comment 表说明
FROM
information_schema. TABLES a
WHERE
a.table_schema = '数据库名' and a.table_comment = ''
查询表名、表注释、字段名、字段注释、字段类型
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 = '数据库名' and b.column_comment !=''
ORDER BY
a.table_name
查询数据库中表的记录数量
SELECT table_name, table_rows FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'schoolroll' ORDER BY table_rows DESC;
oracle
-- 查询所有用户的表,视图等。 (查询某用户下的表、视图等)
select * from all_tab_comments where owner='用户';
-- 查询本用户的表,视图等。
select * from user_tab_comments
--查询所有用户的表的列名和注释。
select * from all_col_comments
-- 查询本用户的表的列名和注释。
select * from user_col_comments
--查询所有用户的表的列名等信息。
select * from all_tab_columns
--查询本用户的表的列名等信息。
select * from user_tab_columns
--表字段、类型
select table_name,column_name,data_type from all_tab_columns where owner='GSMIS' order by table_name;
--表数据量
select TABLE_NAME,NUM_ROWS from all_tables where owner='GSMIS' AND NUM_ROWS <>'0' ORDER BY TABLE_NAME;
--非空表的字段、类型
select table_name,column_name,data_type
from all_tab_columns
where owner='GSMIS' and table_name not in
(select TABLE_NAME from all_tables where owner='GSMIS' and NUM_ROWS=0
) order by table_name;
--表字段值数量占比
SELECT 'XSLBM'AS a ,COUNT(1)||'/'||'31' AS b FROM
(select XSLBM from GSMIS.JF_BLTC T where XSLBM IS NOT NULL)
union
SELECT 'MC' AS a,COUNT(1)||'/'||'31' AS b FROM
(select MC from GSMIS.JF_BLTC T where MC IS NOT NULL)
union
SELECT 'BLLB' AS a,COUNT(1)||'/'||'31' AS b FROM
(select BLLB from GSMIS.JF_BLTC T where BLLB IS NOT NULL)
--查询表字段值数量
select COUNT(1)||'/'||'3162' from GSMIS.PY_JSJBXX_TEST WHERE DW IS NOT NULL;