以前调试刚接手的程序,该程序使用SQL Server ,表很多,上百。常常需要根据某一个特定的值,定位该值在数据库中的什么表的什么位置。对于此需要,我写了下面的代码,用Python连接到SQL Server ,找出所有表,对于表中每个字段值查找,找出目标值。
#write by mrjwt
#need pip install pyodbc
import pyodbc
def listTables():
pass
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=yourserverpathname;DATABASE=yourdbname;UID=sa;PWD=yourpassword')
cursor = cnxn.cursor()
cursor.execute("select name,type,type_desc, create_date,modify_date from sys.tables")
rows = cursor.fetchall()
for row in rows:
#print(row)
tname=row[0]
print(tname)
print(str(tname))
s = str(tname)
sqlstr = "select COLUMN_NAME,DATA_TYPE from INFORMATION_SCHEMA.COLUMNS t where t.TABLE_NAME = '" +s+ "';"
cursor2 = cnxn.cursor()
cursor2.execute(sqlstr)
rows2 = cursor2.fetchall()
for row2 in rows2:
print(row2)
cursor2.close()
cursor.close()
cnxn.close()
def foundInTables( c, v):
pass
colName=str(c)
value=str(v)
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=yourserverpathname;DATABASE=yourdbname;UID=sa;PWD=yourpassword')
cursor = cnxn.cursor()
sqls = "select TABLE_NAME from INFORMATION_SCHEMA.COLUMNS t where t.COLUMN_NAME = '" + colName + "';"
cursor.execute(sqls)
rows = cursor.fetchall()
for row in rows:
tname=str(row[0])
print("TableName:"+tname)
sqlstr = "select * from "+ tname + " t where t."+ colName + " = '" + value + "';"
cursor2 = cnxn.cursor()
cursor2.execute(sqlstr)
rows2 = cursor2.fetchall()
for row2 in rows2:
print(row2)
cursor2.close()
cursor.close()
cnxn.close()
def foundValueInTables(v):
pass
print("#### \nTo find in tables : " + v+'\n####\n')
value=unicode(v)
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=yourserverpathname;DATABASE=yourdbname;UID=sa;PWD=yourpassword')
cursor = cnxn.cursor()
sqls = "select DISTINCT TABLE_NAME from INFORMATION_SCHEMA.COLUMNS ;"
#sqls = "select name from sys.tables"
cursor.execute(sqls)
rows = cursor.fetchall()
for row in rows:
tname=str(row[0])
foundValueInTable(value,tname,cnxn)
cursor.close()
cnxn.close()
def foundValueInTable(v,tname,con):
pass
sqlstr = "select * from "+ tname + ";"
cursor2 = con.cursor()
cursor2.execute(sqlstr)
rows2 = cursor2.fetchall()
bfound=False
for row2 in rows2:
for colval in row2:
pass
if(unicode(colval)==v):
bfound=True
print(row2)
break
if(bfound==True):
print("\nFound in TableName: "+tname+"\n\n")
cursor2.close()
if __name__ == '__main__':
pass
#listTables()
foundValueInTables("CPText_039")