两个Python脚本轻松解决ETL工作：统计多个服务器下所有数据表信息(1)

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’)]

f