阅读量:3
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…except删除
try:
databases_name.remove(“ReportServer”)
databases_name.remove(“ReportServerTempDB”)
except Exception as e:
print(e)
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效果：查看表名所对应建表时间、数据表总行数、数据表总大小、使用大小、未使用大小
