最近要使用模板引擎根据数据库表生成DAO 、service、controller等文件,需要查询对应数据库的所有表,以下是找到比较常用的三种数据库查询表的方法(只查询了表明和别名其实查询 * 可以发现还有很多其他的信息,但是我只用到这两个字段)
1.Oracle
SELECT t.TABLE_NAME AS NAME,
c.COMMENTS AS comments
FROM
user_tables t,
user_tab_comments c
WHERE
t.table_name = c.table_name
c.COMMENTS AS comments
FROM
user_tables t,
user_tab_comments c
WHERE
t.table_name = c.table_name
2.sqlserver
SELECT
t. NAME AS NAME,
b.
VALUE
AS comments
FROM
sys.objects t
LEFT JOIN sys.extended_properties b ON b.major_id = t.object_id
AND b.minor_id = 0
AND b.class = 1
AND b. NAME = 'MS_Description'
WHERE
t.type = 'U'
t. NAME AS NAME,
b.
VALUE
AS comments
FROM
sys.objects t
LEFT JOIN sys.extended_properties b ON b.major_id = t.object_id
AND b.minor_id = 0
AND b.class = 1
AND b. NAME = 'MS_Description'
WHERE
t.type = 'U'
3.mysql
SELECT
t.table_name AS NAME,
t.TABLE_COMMENT AS comments
FROM
information_schema.`TABLES` t
WHERE
t.TABLE_SCHEMA = (SELECT DATABASE())
t.table_name AS NAME,
t.TABLE_COMMENT AS comments
FROM
information_schema.`TABLES` t
WHERE
t.TABLE_SCHEMA = (SELECT DATABASE())
下图是mysql的运行结果