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

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效果：查看表名所对应建表时间、数据表总行数、数据表总大小、使用大小、未使用大小