生成数据字典，早年写的，请注意新的版本变化。
（1）MySQL元数据SQL
USE information_schema;
#取出库和表。
select
TABLE_SCHEMA AS '数据库名称',
TABLE_NAME AS '表名',
TABLE_TYPE AS '表类型',
ROW_FORMAT AS '行格式',
ENGINE AS '数据库引擎',
TABLE_COMMENT AS '表注释'
from TABLES
where TABLE_SCHEMA not in ('mysql','sys','information_schema','performance_schema')
and TABLE_TYPE='BASE TABLE';
SELECT
C.COLUMN_NAME AS '字段名',
C.COLUMN_TYPE AS '数据类型',
C.COLUMN_DEFAULT AS '默认',
C.IS_NULLABLE AS '允许为空',
C.COLUMN_KEY AS '键类型',
C.EXTRA AS '自增属性',
C.CHARACTER_SET_NAME AS '编码名称',
C.COLUMN_COMMENT AS '字段说明'
FROM
COLUMNS C
INNER JOIN TABLES T ON C.TABLE_SCHEMA = T.TABLE_SCHEMA
AND C.TABLE_NAME = T.TABLE_NAME
WHERE
T.TABLE_SCHEMA = 'xxx'
AND T.TABLE_NAME='sysadmin'
SELECT
T.TABLE_SCHEMA AS '数据库名称',
T.TABLE_NAME AS '表名',
T.TABLE_TYPE AS '表类型',
T. ENGINE AS '数据库引擎',
C.ORDINAL_POSITION AS '字段编号',
C.COLUMN_NAME AS '字段名',
C.COLUMN_TYPE AS '数据类型',
C.IS_NULLABLE AS '允许为空',
C.COLUMN_KEY AS '键类型',
C.EXTRA AS '自增属性',
C.CHARACTER_SET_NAME AS '编码名称',
C.COLUMN_COMMENT AS '字段说明'
FROM
COLUMNS C
INNER JOIN TABLES T ON C.TABLE_SCHEMA = T.TABLE_SCHEMA
AND C.TABLE_NAME = T.TABLE_NAME
WHERE
T.TABLE_SCHEMA = 'mysql'
（2）SQL Server数据字典SQL
SQL Server 2008数据库，生成数据字典比较麻烦:
use YourDatabase --指定要生成数据字典的数据库
go
SELECT
表名=case when a.colorder=1 then d.name else '' end,
表说明=case when a.colorder=1 then isnull(f.value,'') else '' end,
字段序号=a.colorder,
字段名=a.name,
标识=case when COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
))) then '√' else '' end,
类型=b.name,
占用字节数=a.length,
长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空=case when a.isnullable=1 then '√'else '' end,
默认值=isnull(e.text,''),
字段说明=isnull(g.[value],'')
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id
left join sys.extended_properties f on d.id=f.major_id and f.minor_id =0
--where d.name='要查询的表' --如果只查询指定表,加上此条件
order by a.id,a.colorder
（3）oracle数据字典SQL
读取Oracle表字段名字，类型（含长度），注释等信息的SQL语句：
你想自动生成数据字典，可以先用以下语句获得所有表名.
SELECT A.*,B.comments
FROM all_tab_columns A,DBA_COL_COMMENTS B
WHERE A.owner=B.owner
AND A.table_name=B.table_name
AND A.COLUMN_NAME=B.COLUMN_NAME
AND A.owner='用户名';
再循环执行下面的语句，就可以生成数据字典了：
select col.COLUMN_NAME, com.Comments, col.DATA_TYPE, col.DATA_LENGTH
from sys.all_tab_columns col,
sys.all_col_comments com
where col.owner = '用户名'
and col.table_name = '表名'
and com.Owner (+) = '用户名'
and com.Table_Name (+) = '表名'
and com.Column_Name (+) = col.Column_Name
order by col.column_id
也可以一次过读出该用户所有表的字段信息：
SELECT A.table_name,A.COLUMN_NAME,A.DATA_TYPE,
a.DATA_LENGTH,a.DATA_PRECISION,
a.DATA_SCALE,a.NULLABLE,a.DATA_DEFAULT,B.comments
FROM all_tab_columns A,DBA_COL_COMMENTS B
WHERE A.owner=B.owner
AND A.table_name=B.table_name
AND A.COLUMN_NAME=B.COLUMN_NAME
AND A.owner='用户名'
ORDER BY A.table_name;
==============================python代码==============
（1）mysql版本
#encoding:utf-8
#Author:lzj
#Date:2018-09-07
#Description:连接数据库，生成Excel数据字典。
import os,sys
dir_path = os.path.dirname(os.path.realpath(__file__))
parent_dir = os.path.dirname(dir_path) #将上级目录添加到系统路径下。
sys.path.append(os.path.dirname(parent_dir))
from include import my_log
from include.db.conn import db_conn
import xlwt
import time
class data_cov(object):
def __init__(self):
mylog = my_log.my_log("ALL")
self.logger = mylog.init_log('data_dict.log')
cnf = db_conn.CONN('pymysql',os.path.join(dir_path,'mysql.cnf'))
conn = cnf.create_conn()
self.cur = conn.cursor()
self.info = cnf.connect_info()
#初始化Excel表格
self.wb = xlwt.Workbook(encoding='utf-8')
def get_schema_data(self,db_list,max_len=0):
if not isinstance(db_list,list) or len(db_list) == 0:
self.logger.error('Must be a list and have at least one schema.')
sys.exit(2)
else:
for db in db_list:
self.logger.info( 'schema %s is writing...' % db)
table_list_sql=("select "
"`TABLE_NAME`,TABLE_COMMENT,'','',"
"ENGINE,ROW_FORMAT, TABLE_COLLATION,'' "
"from information_schema.TABLES "
"where TABLE_SCHEMA ='%s' "
"and TABLE_TYPE='BASE TABLE' order by TABLE_NAME;" % db)
self.cur.execute(table_list_sql)
table_list_data = self.cur.fetchall()
#每一个schema，一个sheet，多个就生成多个sheet。
start_row = 0 # 行num计数,用于每个表按行往下填充。
sheet = self.wb.add_sheet( db, cell_overwrite_ok=True )
#设置Execl样式：列宽设置。
sheet.col(0).width=256*30
sheet.col(1).width =256*15
sheet.col(2).width =256*15
sheet.col(3).width=256*9
sheet.col(4).width=256*9
sheet.col(5).width =256*15
sheet.col(6).width =256*20
sheet.col(7).width=256*40
sheet.col(8).width = 256 * 30
#构造easyxf参数。
#设置表头信息样式：font:11号字，微软雅黑字体，pattern:背景颜色，alignment:左对齐，borders:边框。
table_style = xlwt.easyxf('font:name 微软雅黑,height 220;pattern: pattern solid,fore_colour gold;'
'alignment:horizontal left, vertical center;'
'borders:left 2,right 2,top 2,bottom 2')
#设置字段 样式：11号字，微软雅黑字体，背景颜色，左对齐，边框。
col_style = xlwt.easyxf('font:name 微软雅黑,height 220;pattern: pattern solid,fore_colour aqua;'
'alignment:horizontal left, vertical center;'
'borders:left 2,right 2,top 2,bottom 2')
#设置 行 样式：11号字，微软雅黑字体，左对齐，边框。
row_style = xlwt.easyxf('font:name 微软雅黑,height 220;'
'alignment:horizontal left,vertical center;'
'borders:left 2,right 2,top 2,bottom 2')
#获取每个表的结构信息。
for table_info in table_list_data:
table_name = table_info[0]
self.logger.info( 'table %s start...' % table_name )
column_list_sql='''SELECT
C.COLUMN_NAME,
C.COLUMN_TYPE,
C.COLUMN_DEFAULT,
C.IS_NULLABLE,
C.COLUMN_KEY,
C.EXTRA,
C.COLLATION_NAME,
C.COLUMN_COMMENT,
C.DATA_TYPE
FROM
INFORMATION_SCHEMA.COLUMNS C
WHERE
C.TABLE_SCHEMA = '{table_schema}'
AND C.TABLE_NAME='{table_name}' order by ordinal_position
'''.format(table_schema=db,table_name=table_name)
self.cur.execute(column_list_sql)
column_list_data = self.cur.fetchall()
tmp_data_list = []
for row_data in column_list_data:
if row_data[-1] in ['int','bigint','smallint','tinyint']:
len_sql = "select max(length(%s)) from %s.%s" % (row_data[0],db,table_name)
elif row_data[-1] in ['varchar','char']:
len_sql = "select max(length(%s)) from %s.%s" % (row_data[0],db,table_name)
# write table info
# 表名 注释 2个留空 行格式 引擎 字符编码 留空
#('city_info', '城市信息', '', '', 'Dynamic', 'InnoDB', 'utf8mb4_general_ci','')
for table_col_num in range(0,len(table_info)):
sheet.write(start_row,table_col_num,table_info[table_col_num],table_style)
start_row+=1
# write col_name
columns_name=['字段名称','字段类型','默认值','允许为空','键类型','自增属性','字符编码','注释']
for col_name_num in range(0,len(columns_name)):
sheet.write(start_row,col_name_num,columns_name[col_name_num],col_style)
start_row += 1
#write row data
# 字段 字段类型 默认值 是否为空 键类型 自增 字符编码 注释
#('id', 'int(11)', None, 'NO', 'PRI', 'auto_increment', None, '')
nrow=start_row
for row in range(nrow,len(column_list_data)+nrow):
start_row+=1
for col in range(0,len(columns_name)):
sheet.write(row,col,column_list_data[row-nrow][col],row_style)
self.logger.info('%s write ok.' % table_name)
start_row+=1
self.logger.info('schema %s write ok.' % db )
w_date = time.strftime( "%Y%m%d", time.localtime( time.time()))
self.wb.save(db+'_'+w_date+'.xls')
def get_all_schema(self):
get_all_db_sql = "SELECT DISTINCT TABLE_SCHEMA FROM INFORMATION_SCHEMA.`TABLES` where TABLE_SCHEMA " \
"not in ('INFORMATION_SCHEMA','METRICS_SCHEMA','PERFORMANCE_SCHEMA','mysql','test') order by TABLE_SCHEMA"
self.cur.execute(get_all_db_sql)
db_list_data = self.cur.fetchall()
all_db_list = []
for i in db_list_data:
all_db_list.append(i[0])
return all_db_list
def get_crisps_schema(self):
get_all_db_sql = "SELECT DISTINCT TABLE_SCHEMA FROM INFORMATION_SCHEMA.`TABLES` where TABLE_SCHEMA like 'crisps%' order by TABLE_SCHEMA " \
""
self.cur.execute(get_all_db_sql)
db_list_data = self.cur.fetchall()
all_db_list = []
for i in db_list_data:
all_db_list.append(i[0])
return all_db_list
def get_connect_info(self):
self.info.pop('passwd')
self.logger.info('connect info : %s' % self.info)
if __name__ == '__main__':
init = data_cov()
init.get_connect_info()
#获取所有schema。
db_list = init.get_all_schema()
init.get_schema_data(db_list)
#获取所有crisps的schema。
# db_list = init.get_crisps_schema()
# init.get_schema_data(db_list)
#指定schema
# init.get_schema_data(['company_info'])（2）Oracle版本
#encoding:utf-8
#Author:lzj
#Date:2018-09-07
#Description:连接数据库，生成Excel数据字典。
import os,sys
dir_path = os.path.dirname(os.path.realpath(__file__))
parent_dir = os.path.dirname(dir_path) #将上级目录添加到系统路径下。
sys.path.append(os.path.dirname(parent_dir))
from include import my_log
import xlwt
import time
import xlwt_xls_format #引入Excel样式函数
import configparser
import cx_Oracle
#定义解析函数
def get_config(group,config_name,config_file=None):
if config_file is None:
config = configparser.ConfigParser()
path=os.path.join(parent_dir,'conf')
config.read_file(open(os.path.join(path,'mysql.cnf')),'r')
config_value=config.get(group,config_name).strip(' ').strip('\'').strip('\"')
return config_value
else:
config = configparser.ConfigParser()
config.read_file(open(config_file),'r')
config_value=config.get(group,config_name).strip(' ').strip('\'').strip('\"')
return config_value
#创建链接
def init_conn(config_file=None):
ora_user=get_config('oracle','user',config_file)
ora_passwd=get_config('oracle','passwd',config_file)
ora_host=get_config('oracle','host',config_file)
ora_port=int(get_config('oracle','port',config_file))
ora_sid=get_config('oracle','sid',config_file)
#ora_charset=get_config('oracle','charset',config_file)
try:
#conn = cx_Oracle.connect('用户名/密码@IP:端口号/SERVICE_NAME')
conn = cx_Oracle.connect(ora_user+'/'+ora_passwd+'@'+ora_host+':'+str(ora_port)+'/'+ora_sid)
return conn
except Exception as e:
print (e)
sys.exit()
#
class data_cov(object):
def __init__(self):
mylog = my_log.my_log("ALL")
self.logger = mylog.init_log('data_dict.log')
self.init = init_conn(config_file=os.path.join(dir_path,'mysql.cnf'))
self.cur = self.init.cursor()
#初始化Excel表格
self.wb = xlwt.Workbook(encoding='utf-8')
self.USER=get_config('oracle','user',os.path.join(dir_path,'mysql.cnf'))
def get_schema_data(self):
self.logger.info( 'schema %s is writing...' % self.USER)
table_list_sql="SELECT TABLE_NAME,COMMENTS,'','','','','AMERICA.AL32UTF8' as TABLE_COLLATION FROM USER_TAB_COMMENTS WHERE TABLE_TYPE='TABLE'"
self.cur.execute(table_list_sql)
table_list_data = self.cur.fetchall()
if table_list_data:
#每一个schema，一个sheet，多个就生成多个sheet。
start_row = 0 # 行num计数,用于每个表按行往下填充。
sheet = self.wb.add_sheet(self.USER, cell_overwrite_ok=True )
#设置Execl样式：列宽设置。
sheet.col(0).width=256*30
sheet.col(1).width =256*25
sheet.col(2).width =256*15
sheet.col(3).width=256*9
sheet.col(4).width=256*9
sheet.col(5).width =256*15
sheet.col(6).width =256*25
sheet.col(7).width=256*40
#引入字体，边框，对齐，背景等样式设置，还可以简化使用easyxf来配置
#table_style = xlwt_xls_format.set_style('table')
#style = xlwt_xls_format.set_style()
#构造easyxf参数。
#设置表头信息样式：font:11号字，微软雅黑字体，pattern:背景颜色，alignment:左对齐，borders:边框。
table_style = xlwt.easyxf('font:name 微软雅黑,height 220;pattern: pattern solid,fore_colour gold;'
'alignment:horizontal left, vertical center;'
'borders:left 2,right 2,top 2,bottom 2')
#设置字段 样式：11号字，微软雅黑字体，背景颜色，左对齐，边框。
col_style = xlwt.easyxf('font:name 微软雅黑,height 220;pattern: pattern solid,fore_colour aqua;'
'alignment:horizontal left, vertical center;'
'borders:left 2,right 2,top 2,bottom 2')
#设置 行 样式：11号字，微软雅黑字体，左对齐，边框。
row_style = xlwt.easyxf('font:name 微软雅黑,height 220;'
'alignment:horizontal left,vertical center;'
'borders:left 2,right 2,top 2,bottom 2')
#获取每个表的结构信息。
for table_info in table_list_data:
table_name = table_info[0]
self.logger.info( 'table %s start...' % table_name )
column_list_sql='''SELECT A.COLUMN_NAME,
DECODE(A.CHAR_LENGTH,0,
DECODE(A.DATA_SCALE,NULL,A.DATA_TYPE,
A.DATA_TYPE || '(' || A.DATA_PRECISION || ',' ||
A.DATA_SCALE || ')'),
A.DATA_TYPE || '(' || A.CHAR_LENGTH || ')') as COLUMN_TYPE,
A.DATA_DEFAULT AS COLUMN_DEFAULT,
A.NULLABLE,
S.column_key,
'' as extra,
'AMERICA.AL32UTF8' as COLLATION_NAME,
B.comments
FROM sys.all_tab_columns A
join sys.DBA_COL_COMMENTS B on A.owner = B.owner AND A.table_name = B.table_name AND A.COLUMN_NAME = B.COLUMN_NAME
LEFT JOIN (
select c.table_name,c.COLUMN_NAME,case D.constraint_type when 'P' then 'PRI' else '' end as column_key
from user_cons_columns C, user_constraints D
where C.constraint_name = D.constraint_name
and D.constraint_type = 'P'
) S on S.table_name = A.table_name and S.COLUMN_NAME = A.COLUMN_NAME
WHERE A.owner = '{table_schema}'
and A.TABLE_NAME='{table_name}'
'''.format(table_schema=self.USER.upper(),table_name=table_name)
self.cur.execute(column_list_sql)
column_list_data = self.cur.fetchall()
#print (column_list_data)
#写入表信息
# 表名 注释 2个留空 行格式 引擎 字符编码 留空
#('city_info', '城市信息', '', '', 'Dynamic', 'InnoDB', 'utf8mb4_general_ci','')
for table_col_num in range(0,len(table_info)):
sheet.write(start_row,table_col_num,table_info[table_col_num],table_style)
start_row+=1 #从下一行开始
#写入列名
columns_name=['字段名称','字段类型','默认值','允许为空','键类型','自增属性','字符编码','注释']
for col_name_num in range(0,len(columns_name)):
sheet.write(start_row,col_name_num,columns_name[col_name_num],col_style)
start_row += 1 # 从下一行开始
#写入行数据
# 字段 字段类型 默认值 是否为空 键类型 自增 字符编码 注释
#('id', 'int(11)', None, 'NO', 'PRI', 'auto_increment', None, '')
nrow=start_row
for row in range(nrow,len(column_list_data)+nrow):
start_row+=1
for col in range(0,len(columns_name)):
sheet.write(row,col,column_list_data[row-nrow][col],row_style)
self.logger.info('%s write ok.' % table_name)
start_row+=1 #每个表空一行
self.logger.info('schema %s write ok.' % self.USER )
self.cur.close()
self.init.close()
w_date = time.strftime( "%Y%m%d", time.localtime( time.time()))
self.wb.save('数据字典_'+w_date+'.xls')
if __name__ == '__main__':
init = data_cov()
init.get_schema_data()
#init.get_schema_data(['business','contract','health','sso','total'])
#init.get_schema_data(['ly'])
（3）样例
