MySql查询数据库所有表名与主键
MySql查询数据库所有表名与主键
数据库名称为“dataBase1“
1.查询数据库中的表名,表注释,表数据量
SELECT
TABLE_NAME 表名,TABLE_COMMENT 表注释,TABLE_ROWS 数据量
FROM information_schema.tables
WHERE TABLE_SCHEMA = 'dataBase1'
ORDER BY TABLE_NAME;
2. 查询数据库中表 ‘table1’ 所有字段注释
SELECT
COLUMN_NAME 字段名,column_comment 字段注释
FROM INFORMATION_SCHEMA.Columns
WHERE table_name='table1' AND table_schema='dataBase1'
3. 查询数据库中所有表的表名、表注释以及对应表字段注释
SELECT
a.TABLE_NAME 表名,a.TABLE_COMMENT 表注释,b.COLUMN_NAME 表字段,b.COLUMN_TYPE 字段类型,b.COLUMN_COMMENT
字段注释
FROM information_schema.TABLES a,INFORMATION_SCHEMA.Columns b
WHERE b.TABLE_NAME=a.TABLE_NAME AND a.TABLE_SCHEMA='dataBase1'
4.查询数据库中表table1中的主键
SELECT TABLE_NAME,COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE constraint_name = 'PRIMARY' AND TABLE_NAME = 'table1' AND table_schema = 'dataBase1'
5、查询表中所有外键
SELECT TABLE_NAME,COLUMN_NAME,referenced_table_name 被引用的表,referenced_column_name 被引用的字段 FROM information_schema.KEY_COLUMN_USAGE WHERE referenced_column_name is not NULL AND table_schema = 'dataBase1'
更多推荐
所有评论(0)