#coding=utf-8

import MSSQL

db2010 = MSSQL.MSSQL(host=r"192.168.1.100",user="sa",pwd="`12345",db="Con2010_zjhw")
db2016 = MSSQL.MSSQL(host=r"192.168.1.100\MSSQLSERVERR2",user="sa", \
                     pwd="`12345",db="Con2016")

def FindNotIn():
    
    sqlalltable=r"SELECT Name FROM SysObjects Where XType='U' ORDER BY Name"
    
    tablelist2010 = ToList(db2010.ExecQuery(sqlalltable))
    tablelist2016 = ToList(db2016.ExecQuery(sqlalltable)) 

    TnotIn=[]
    CnotIn={}
    
    for table in tablelist2010:
        if table not in tablelist2016:
            TnotIn.append(table)
        else:
            sqlallCol=r"SELECT Name FROM SysColumns WHERE id=Object_Id('%s')" %table
            col2010=ToList(db2010.ExecQuery(sqlallCol))
            col2016=ToList(db2016.ExecQuery(sqlallCol))

            collist=[]
            for col in col2010:
                if col not in col2016:
                    collist.append(col)
            if len(collist)>0:
                CnotIn[table]=collist
    return TnotIn,CnotIn

def FindNotIn_TR_P_FN(TR_P_FN):
    sql="SELECT * FROM Sysobjects WHERE xtype = '%s'" %TR_P_FN
    list2010 = ToList(db2010.ExecQuery(sql))
    list2016 = ToList(db2016.ExecQuery(sql))

    notin=[]
    notsame=[]
    
    for x in list2010:
        if x not in list2016:
            notin.append(x)
        else:
            sqltxt="EXEC sp_helptext '%s'" %x
            txt2010=ToStr(db2010.ExecQuery(sqltxt))
            txt2016=ToStr(db2016.ExecQuery(sqltxt))
            if txt2010!=txt2016:
                notsame.append(x)
    return notin,notsame
    

def ToList(listresult):
    result=[]
    for x in listresult:
        result.append (x[0])   
    return result

def ToStr(listresult):
    result=""
    for x in listresult:
        result+=x[0].strip()
    return result

if __name__ == '__main__':   
    t10=ToStr(db2010.ExecQuery("EXEC sp_helptext 'sp_qcc010305'"))
    t16=ToStr(db2016.ExecQuery("EXEC sp_helptext 'sp_qcc010305'"))
    if t10==t16:
        print("ok")
    else:
        print("no")