通过sql语句生成MySQl数据字典代码,MYSQL代码库,德仔网

代码语言

代码分类

【MYSQL】通过sql语句生成MySQl数据字典

作者: / 发布于2016/11/28/ 582

select distinct a.TABLE_SCHEMA as '数据库' ,
a.TABLE_NAME as '表名',
a.COLUMN_NAME as '字段名',
a.COLUMN_TYPE as '类型长度',
case when a.IS_NULLABLE='yes' then '√'else '' end as '允许空值',
case when a.COLUMN_DEFAULT='' then '""' else a.COLUMN_DEFAULT end  as '默认值',
a.CHARACTER_SET_NAME as '字符集',
a.COLLATION_NAME as '整理',
a.EXTRA as '自动递增',
case when b.CONSTRAINT_NAME='PRIMARY'  then 'PRIMARY' else '' end  as '主键',
c.CONSTRAINT_NAME  as '外键名',
c.REFERENCED_TABLE_NAME as '关联父表',
c.REFERENCED_COLUMN_NAME as '父表字段',
d. CONSTRAINT_NAME as '索引名称'
from INFORMATION_SCHEMA.COLUMNS as a
left join (select CONSTRAINT_NAME,TABLE_NAME table_name2,COLUMN_NAME col_name2 from INFORMATION_SCHEMA .KEY_COLUMN_USAGE where  CONSTRAINT_NAME='PRIMARY' and table_name like 't_sale%') as b
on a.TABLE_NAME=b.table_name2 and a.COLUMN_NAME=b.col_name2
left join (select CONSTRAINT_NAME,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME,TABLE_NAME table_name3,COLUMN_NAME col_name3 from INFORMATION_SCHEMA .KEY_COLUMN_USAGE where REFERENCED_COLUMN_NAME!='' and table_name like 't_sale%') as c
on a.TABLE_NAME=c.table_name3 and a.COLUMN_NAME=c.col_name3
left join (select CONSTRAINT_NAME,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME,TABLE_NAME table_name4,COLUMN_NAME col_name4 from INFORMATION_SCHEMA .KEY_COLUMN_USAGE where REFERENCED_COLUMN_NAME is null and CONSTRAINT_NAME!='PRIMARY' and table_name like 't_sale%')
as d 
on a.TABLE_NAME=d.table_name4 and a.COLUMN_NAME=d.col_name4
where table_name ='t_sale_agent'

试试其它关键字

同语言下

可能有用的

贡献的其它代码Label