SELECT TABLE_NAME as table_name,
TABLE_ROWS as table_rows,
TABLE_COMMENT as table_comment
FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA ='数据库名'
获取该表的字段信息(字段名,字段类型,字段注释)
SELECT COLUMN_NAME as col,
COLUMN_TYPE astype,
COLUMN_COMMENT as fieldDesc
FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME ='表名'
2.Orancle
获取该数据库的表(表名,行数,表注释)
SELECT a.table_name AS"table_name", a.num_rows AS"table_rows",b.comments AS"table_comment"FROM user_tables a
LEFTJOIN USER_TAB_COMMENTS b ON a.TABLE_NAME = b.TABLE_NAME
WHERE a.TABLESPACE_NAME!='SYSAUX'and a.TABLESPACE_NAME!='EXAMPLE'and a.table_name notlike'%$%'
获取该表的字段信息(字段名,字段类型,字段注释)
SELECT a.COLUMN_NAME AS"col",a.COMMENTS AS"fieldDesc",b.DATA_TYPE AS"type"FROM user_col_comments a
LEFTJOIN all_tab_columns b ON a.COLUMN_NAME = b.COLUMN_NAME AND a.TABLE_name = b.TABLE_name
WHERE a.TABLE_name ='表名'
3.sql Server
获取该数据库的表(表名,行数,表注释)
SELECT t.name AS table_name,SUM(p.rows)AS table_rows,c.valueAS table_comment
FROM sys.tables t
INNERJOIN sys.partitions p ON t.object_id = p.object_id
leftjoin sys.extended_properties c on c.major_id = t.object_id
WHERE t.is_ms_shipped =0AND p.index_id IN(0,1)GROUPBY t.name,c.valueORDERBY table_rows DESC
获取该表的字段信息(字段名,字段类型,字段注释)
select b.name as col, c.valueas column_description ,d.data_type astypefrom sys.tables a
innerjoin sys.columns b on b.object_id = a.object_id
leftjoin sys.extended_properties c on c.major_id = b.object_id and c.minor_id = b.column_id
leftjoin INFORMATION_SCHEMA.COLUMNS d on d.table_name = a.name and d.column_name = b.name
where a.name ='表名'
4.PostgreSql
获取该数据库的表(表名,行数,表注释)
select f.table_name ,a.table_comment ,e.n_live_tup as table_rows
from information_schema.tables f
leftjoin(SELECT c.relname AS table_name, d.description AS table_comment
FROM pg_class AS c
LEFTJOIN pg_description AS d ON(c.oid = d.objoid AND d.objsubid =0)) a on a.table_name = f.table_name
leftJOIN pg_stat_user_tables e on f.table_name = e.relname
where f.table_schema ='public'
获取该表的字段信息(字段名,字段类型,字段注释)
SELECT a.attname as col,
format_type(a.atttypid,a.atttypmod)astype,
col_description(a.attrelid,a.attnum)as fieldDesc
FROM pg_class as c,pg_attribute as a
where
a.attrelid = c.oid and
a.attnum>0and
c.relname ='表名'