正文
print(databases_name)
return databases_name
- 提取服务器下所有数据库名，用于后续SQL语句的拼接，利用列表的
remove方法移除不需要的数据库
3）获取表详细信息并保存
def save(self, databases_name, cursor, server_name):
“”“获取表信息并保存”“”
加入count是为了换行写入数据
count = self.count databases_name:[‘master’, ‘tempdb’, ‘model’, ‘msdb’, ‘ReportServer’, ‘ReportServerTempDB’, ‘test’]
取出每个库名，用于拼接sql，获取对应库名下表信息
for database in databases_name: sql = ‘’’ USE [%s] SELECT a.name table_name, a.crdate crdate, b.rows rows, 8*b.reserved/1024 reserved, rtrim(8*b.dpages/1024) used, 8*(b.reserved-b.dpages)/1024 unused FROM sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.id WHERE ( a.type = ‘u’ ) AND ( b.indid IN ( 0, 1 ) ) ORDER BY a.name,b.rows DESC; ‘’’ % database cursor.execute(sql) rows = cursor.fetchall() # 逐行读取 for i in rows:
要写入excel的数据
server = server_name database_name = database table_name = i[0] crdate = i[1] rows_size = i[2] reserved = i[3] used = i[4] unused = i[5]
打印获取到的数据
print(server, database_name, table_name, crdate, rows_size, reserved, used, unused)
row：count所对应的就是行数，从第二行开始累加，colum：表示第几列，value：表示插入的值
self.wb.cell(row=count, column=1, value=server) self.wb.cell(row=count, column=2, value=database_name) self.wb.cell(row=count, column=3, value=table_name) self.wb.cell(row=count, column=4, value=crdate) self.wb.cell(row=count, column=5, value=rows_size) self.wb.cell(row=count, column=6, value=reserved) self.wb.cell(row=count, column=7, value=used) self.wb.cell(row=count, column=8, value=unused)
count加1，进入到下一行写入数据
count += 1
self.count = count
- 拼接SQL效果：查看表名所对应建表时间、数据表总行数、数据表总大小、使用大小、未使用大小
4）实现主要逻辑
def main(self):
“”“实现主要逻辑”“”
1.创建Excel表对象，设置列名
self.ws = op.Workbook() self.wb = self.ws.create_sheet(index=0) self.wb.cell(row=1, column=1, value=‘服务器地址’) self.wb.cell(row=1, column=2, value=‘库名’) self.wb.cell(row=1, column=3, value=‘表名’) self.wb.cell(row=1, column=4, value=‘建表时间’) self.wb.cell(row=1, column=5, value=‘数据表总行数’) self.wb.cell(row=1, column=6, value=‘数据表总大小(MB)’) self.wb.cell(row=1, column=7, value=‘使用大小(MB)’) self.wb.cell(row=1, column=8, value=‘未使用大小(MB)’) self.count = 2 # 定义为全局变量每次用完会更新
服务器列表,括号内为：服务器名、账号、密码
如果多个服务器元组用逗号隔开
list = [(‘.’, ‘sa’, ‘yuan427’)]
2.遍历服务器列表，实现统计多个服务器
for server in list: server_name = server[0] user_name = server[1] password = server[2] conn = pymssql.connect(server_name, user_name, password) if conn: print(“连接成功!”) cursor = conn.cursor()
3.获取库名
databases_name = self.get_databases_name(cursor)
4.获取详细信息并保存
self.save(databases_name, cursor, server_name)
所有服务器表插入完后保存
excel_name = “./本地数据库统计.xlsx”
self.ws.save(excel_name)
关闭游标,关闭数据库
cursor.close() conn.close()
- 1.在服务器列表循环外创建Excel文件
- 2.遍历服务器列表，实现统计多个服务器
- 3.获取当前遍历服务器的所有库名
- 4.用库名拼接SQL实现获取数据表详细信息
- 5.换行保存在Excel文件
完整代码
需要修改的地方只有服务器地址、账号、密码，每一个服务器信息放一个元组中；如果有多个服务器用逗号隔开
import pymssql import openpyxl as op class ErTransUtils(): def get_databases_name(self, cursor): “”“获取服务器下所有库名”“” sql = ‘SELECT * FROM sys.sysdatabases’ cursor.execute(sql) rows = cursor.fetchall() # 逐行读取
存储
databases_name = [] for list in rows: databases_name.append(list[0])
移除系统库
databases_name.remove(“master”) databases_name.remove(“model”) databases_name.remove(“msdb”) databases_name.remove(“tempdb”)
移除无用库
try: databases_name.remove(“ReportServer”) databases_name.remove(“ReportServerTempDB”) except Exception as e: print(e) print(databases_name) return databases_name def save(self, databases_name, cursor, server_name): “”“获取表信息并保存”“”
加入count是为了换行写入数据
count = self.count
databases_name:[‘master’, ‘tempdb’, ‘model’, ‘msdb’, ‘ReportServer’, ‘ReportServerTempDB’, ‘test’],取出每个库名
for database in databases_name: sql = ‘’’ USE [%s] SELECT a.name table_name, a.crdate crdate, b.rows rows, 8*b.reserved/1024 reserved, rtrim(8*b.dpages/1024) used, 8*(b.reserved-b.dpages)/1024 unused FROM sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.id WHERE ( a.type = ‘u’ ) AND ( b.indid IN ( 0, 1 ) ) ORDER BY a.name,b.rows DESC; ‘’’ % database cursor.execute(sql) rows = cursor.fetchall() # 逐行读取 for i in rows:
要写入excel的数据
server = server_name database_name = database table_name = i[0] crdate = i[1] rows_size = i[2] reserved = i[3] used = i[4] unused = i[5]
打印获取到的数据
print(server, database_name, table_name, crdate, rows_size, reserved, used, unused)
self.wb.cell(row=count, column=1, value=server) self.wb.cell(row=count, column=2, value=database_name) self.wb.cell(row=count, column=3, value=table_name) self.wb.cell(row=count, column=4, value=crdate) self.wb.cell(row=count, column=5, value=rows_size) self.wb.cell(row=count, column=6, value=reserved) self.wb.cell(row=count, column=7, value=used) self.wb.cell(row=count, column=8, value=unused)
count加1，进入到下一行写入数据
count += 1 self.count = count def main(self): “”“实现主要逻辑”“”
创建Excel表对象，设置列名
self.ws = op.Workbook() self.wb = self.ws.create_sheet(index=0) self.wb.cell(row=1, column=1, value=‘服务器地址’) self.wb.cell(row=1, column=2, value=‘库名’) self.wb.cell(row=1, column=3, value=‘表名’) self.wb.cell(row=1, column=4, value=‘建表时间’) self.wb.cell(row=1, column=5, value=‘数据表总行数’) self.wb.cell(row=1, column=6, value=‘数据表总大小(MB)’) self.wb.cell(row=1, column=7, value=‘使用大小(MB)’) self.wb.cell(row=1, column=8, value=‘未使用大小(MB)’) self.count = 2
服务器列表,括号内为：服务器名、账号、密码
如果多个服务器用逗号隔开
list = [(‘.’, ‘sa’, ‘yuan427’)] for server in list: server_name = server[0] user_name = server[1] password = server[2] conn = pymssql.connect(server_name, user_name, password) if conn: print(“连接成功!”) cursor = conn.cursor() databases_name = self.get_databases_name(cursor) self.save(databases_name, cursor, server_name)
所有服务器表插入完后保存
excel_name = “./本地数据库统计.xlsx” self.ws.save(excel_name)
关闭游标,关闭数据库
cursor.close() conn.close() if name == ‘main’: er = ErTransUtils() er.main()
脚本2
=====================================================================
效果展示
自动创建服务器文件夹，服务器文件夹下是所有库文件夹，库文件夹下是以表名命名的Excel文件名，文件中有表字段名称、是否为主键、字段类型、字段长度、索引名称等。
- 我本地的test库
- 表中字段信息如下，代码设置了Excel表格式，网格根据字段数量自动填充
代码解析
由于和第一个脚本相似只讲讲思路：
- 1.获取所有数据库名
- 2.获取库中所有表名，把库名和表名存放在元组内放入列表，如：
[('test', 'student', 'UTIL_IP1', 'test4', 'test5', 'test6', 'TM_AP', 'test1', 'test2', 'test3', 'UTIL_IP')]，第一个是库名其他都是表名 - 3.拼接获取字段信息的SQL，把库名、表名传进去，SQL能获取到的信息如下图（拼接的地方为上面的库名和红框那的表名）：
- 4.设置Excel内格式：字体、加粗、居中、合并、网格线、行高、列宽等
- 5.一个Excel文件保存完毕，生成另一个表的Excel文件，只到当前服务器下所有表统计完毕，才开始统计另一个服务器
完整代码
需要修改的地方只有服务器地址、账号、密码，每一个服务器信息放一个元组中；如果有多个服务器用逗号隔开
import pymssql import openpyxl as op from openpyxl.styles import Font, Alignment, Side, Border import os class ErTransUtils(): def get_databases_name(self, cursor): “”“获取服务器下所有库名”“” sql = ‘SELECT * FROM sys.sysdatabases’ cursor.execute(sql) rows = cursor.fetchall() # 逐行读取
存储
databases_name = [] for list in rows: databases_name.append(list[0])
移除系统库和无用库
databases_name.remove(“master”) databases_name.remove(“model”) databases_name.remove(“msdb”) databases_name.remove(“tempdb”) try: databases_name.remove(“ReportServer”) databases_name.remove(“ReportServerTempDB”) except Exception as e: print(e)
print(databases_name)
return databases_name def get_tables_name(self, databases_name, cursor): “”“获取库中所有表名，并把对应的库名和表名存储在一起”“” item1 = [] # 存储 for i in databases_name: sql = f’SELECT * FROM [{i}].sys.tables’ cursor.execute(sql) rows = cursor.fetchall() # 逐行读取 item = [] for j in rows: item.append(j[0]) item.insert(0, i) item1.append(tuple(item)) return item1 def save(self, item1, cursor, server_name): “”“获取表中字段信息，并写入Excel”“”
根据服务器名称创建目录
os.makedirs(server_name) databases_name:[‘master’, ‘tempdb’, ‘model’, ‘msdb’, ‘ReportServer’, ‘ReportServerTempDB’, ‘test’]
取出每个库名
for database in item1:
根据库名名称创建目录
database1 = f’./{server_name}/{database[0]}’ os.makedirs(database1) print(‘正在统计%s库’ % database[0])
