dbdiff.asp

办公自动化系统

<%
DB1="amber_ease"
DB2="amber_oa"

dim oCon,oRS
set oCon=server.createobject("adodb.connection")
oCon.open "driver={SQL Server};server=127.0.0.1;uid=ease;pwd=Amber_Ease;database=" & DB1 & ";"
set oRS=server.createobject("adodb.recordset")

dim oCon2,oRS2
set oCon2=server.createobject("adodb.connection")
'oCon2.open "driver={SQL Server};server=127.0.0.1;uid=sa;pwd=Holysqlserver@2000;database=" & DB2 & ";"
oCon2.open "driver={SQL Server};server=amber007;uid=sa;pwd=;database=" & DB2 & ";"


Set rstSchema = oCon.OpenSchema(20)

Set rstSchema2 = oCon2.OpenSchema(20)

Do Until rstSchema.EOF
	bFind=false
	rstSchema2.movefirst
	Do Until rstSchema2.EOF or bFind
		if rstSchema("TABLE_NAME") = rstSchema2("TABLE_NAME") then
			 bFind=true
		end if
		'Response.Write "IN DB2 Table name: " & rstSchema2("TABLE_NAME") & vbCr & "Table type: " & rstSchema2("TABLE_TYPE") & vbCr 
		rstSchema2.MoveNext
	Loop
	if not bFind and left(rstSchema("TABLE_NAME"),3)<>"WFD"then
		Response.Write "IN DB:" & DB1 & " DiffTable name: " & rstSchema("TABLE_NAME") & vbCr & "Table type: " & rstSchema("TABLE_TYPE") & vbCr & "<BR>"
	end if
    rstSchema.MoveNext
Loop

response.write "<HR>"
rstSchema2.movefirst

Do Until rstSchema2.EOF
	bFind=false
	rstSchema.movefirst
	Do Until rstSchema.EOF or bFind
		if rstSchema2("TABLE_NAME") = rstSchema("TABLE_NAME") then
			 bFind=true
		end if
		'Response.Write "IN DB2 Table name: " & rstSchema2("TABLE_NAME") & vbCr & "Table type: " & rstSchema2("TABLE_TYPE") & vbCr 
		rstSchema.MoveNext
	Loop
	if not bFind and left(rstSchema2("TABLE_NAME"),3)<>"WFD"then
		Response.Write "IN DB:" & DB2 & " DiffTable name: " & rstSchema2("TABLE_NAME") & vbCr & "Table type: " & rstSchema2("TABLE_TYPE") & vbCr & "<BR>"
	end if
    rstSchema2.MoveNext
Loop
response.write "<HR>不同的字段<HR>"
'--------- 不同的colomn
rstSchema.movefirst
Do Until rstSchema.EOF
	bFind=false
	if instr(lcase(rstSchema("TABLE_type")),"system")=0 then
	rstSchema2.movefirst
	Do Until rstSchema2.EOF or bFind
		last_table=""
		if rstSchema("TABLE_NAME") = rstSchema2("TABLE_NAME")  and instr(lcase(rstSchema2("TABLE_type")),"system")=0 then

			Set objColumnRS = oCon.OpenSchema(4, Array(Empty, Empty, rstSchema("Table_Name").Value))

			Set objColumnRS2 = oCon2.OpenSchema(4, Array(Empty, Empty, rstSchema2("Table_Name").Value))

				Do While Not objColumnRS.EOF

					iLength = objColumnRS("Character_Maximum_Length")
					iPrecision = objColumnRS("Numeric_Precision")
					iScale = objColumnRS("Numeric_Scale")
					iDefaultValue = objColumnRS("Column_Default")
					iColumn_Name =objColumnRS("Column_Name")
					iData_Type = objColumnRS("Data_Type").value
					iIs_Nullable = objColumnRS("Is_Nullable")
					bFindcol=false

					objColumnRS2.movefirst
					while not objColumnRS2.eof and not bFindcol
						
						if iColumn_Name = objColumnRS2("Column_Name") then
							bFindcol = true
							
							If ( iIs_Nullable = objColumnRS2("Is_Nullable").value or ( isnull(iIs_Nullable) and isnull(objColumnRS2("Is_Nullable").value) ) ) and ( iDefaultValue = objColumnRS2("Column_Default") or (isnull(iDefaultValue) and  isnull(objColumnRS2("Column_Default").value))) and ( iData_Type = objColumnRS2("Data_Type") or (isnull(iData_Type) and  isnull(objColumnRS2("Data_Type").value)))  then 

							else
								if last_table<>rstSchema("TABLE_NAME") then 
									last_table=rstSchema("TABLE_NAME") 
									response.write "in table " &  rstSchema("TABLE_NAME") & ":<BR>"
								end if
								response.write "in DB: " & left(db1&"`````````````",10) &  "DIFF COL:" & iColumn_Name &"-->"& "iLength=" & iLength & "iPrecision=" & iPrecision & "iScale=" & iScale & "iDefaultValue=" & iDefaultValue & "iData_Type="& iData_Type & "iIs_Nullable= " & iIs_Nullable & "<BR>"
								response.write "in DB: " &  left(db2&"`````````````",10) &  "DIFF COL:" & objColumnRS("Column_Name") &"-->" & "iLength="&objColumnRS2("Character_Maximum_Length") & "iPrecision=" &  objColumnRS2("Numeric_Precision") & "iScale=" & objColumnRS2("Numeric_Scale") & "iDefaultValue="  & objColumnRS2("Column_Default") & "iData_Type=" & objColumnRS2("Data_Type") & "iIs_Nullable= " & objColumnRS2("Is_Nullable") & "<BR>"
							end if

						end if		

					objColumnRS2.movenext

					wend

					if not bFindcol then 
						if last_table<>rstSchema("TABLE_NAME") then 
								last_table=rstSchema("TABLE_NAME") 
								response.write "in table " &  rstSchema("TABLE_NAME") & ":<BR>"
						end if
						response.write "in DB: "& db1 &  "NEW COL:" & objColumnRS("Column_Name") & iLength & iPrecision & iScale & iDefaultValue & iData_Type & iIs_Nullable & "<BR>"
					end if

					objColumnRS.MoveNext

				Loop

				Set objColumnRS = Nothing
				Set objColumnRS2 = Nothing

			 bFind=true
		end if
		'Response.Write "IN DB2 Table name: " & rstSchema2("TABLE_NAME") & vbCr & "Table type: " & rstSchema2("TABLE_TYPE") & vbCr 
		rstSchema2.MoveNext
	Loop
	end if
    rstSchema.MoveNext
Loop

response.write "<HR>"
'rstSchema.movefirst

Do Until rstSchema.EOF
	bFind=false
	if instr(lcase(rstSchema("TABLE_type")),"system")=0 then
	rstSchema2.movefirst
	Do Until rstSchema2.EOF or bFind
		if rstSchema("TABLE_NAME") = rstSchema2("TABLE_NAME") and instr(lcase(rstSchema2("TABLE_type")),"system")=0 then

			Set objColumnRS2 = oCon.OpenSchema(4, Array(Empty, Empty, rstSchema("Table_Name").Value))

			Set objColumnRS = oCon2.OpenSchema(4, Array(Empty, Empty, rstSchema2("Table_Name").Value))

				objColumnRS2.Movefirst
				Do While Not objColumnRS.EOF
					iColumn_Name =objColumnRS("Column_Name")

					bFindcol=false

					while not objColumnRS2.eof and not bFindcol

						if iColumn_Name = objColumnRS2("Column_Name") then
							bFindcol = true
						end if		
						objColumnRS2.MoveNext
					wend
					if not bFindcol then response.write "in DB: "& db1 &  "NEW COL:" & objColumnRS("Column_Name") & iLength & iPrecision & iScale & iDefaultValue & iData_Type & iIs_Nullable & "<BR>"

				objColumnRS.MoveNext
				Loop

				Set objColumnRS = Nothing
				Set objColumnRS2 = Nothing

			 bFind=true
		end if
		'Response.Write "IN DB2 Table name: " & rstSchema2("TABLE_NAME") & vbCr & "Table type: " & rstSchema2("TABLE_TYPE") & vbCr 
		rstSchema2.MoveNext
	Loop
	end if
    rstSchema.MoveNext
Loop

%>