Database Schema Diff

How to Diff two database schemas in SQL Server:-

A basic approach to diff two databases… Connect to database … extract the metadata and use some function to get source code of procedures to compare..

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;

/**
* @author ravindra.rawat
*
*/
public class TestSchemaDiff {

public static void main(String[] args) throws Exception{
Class.forName(“net.sourceforge.jtds.jdbc.Driver”);
Properties prop = new Properties();
prop.setProperty(“user”,”user”);
prop.setProperty(“password”,”secret”);
prop.setProperty(“instance”,””);

Connection conn = null;
Connection conn2 = null;
try {
conn =
DriverManager.getConnection(“”
, prop);
conn2 =
DriverManager.getConnection(“”, prop);

Statement st = conn.createStatement();
Statement st2 = conn2.createStatement();

// ADD TO xtype for different types you want to diff
ResultSet rs = st.executeQuery(“select name from sysobjects
where xtype = ‘P’ OR xtype =’IF’ order by name”);
ResultSet rs2 = st2.executeQuery(“select name from sysobjects
where xtype = ‘P’ OR xtype =’IF’ order by name”);

List proc1 = new ArrayList();
List proc2 = new ArrayList();

while (rs2.next()) {
proc2.add(rs2.getString(“name”));
}

while (rs.next()) {
proc1.add(rs.getString(“name”));
}

while (rs2.next()) {
proc2.add(rs.getString(“name”));
}

// Now both have all the names

int index = -1;
for (Iterator iter = proc1.iterator(); iter.hasNext();) {
String proc = (String) iter.next();
if (( index = proc2.indexOf(proc)) != -1) {
proc2.remove(index);
if(!compareDefinitions(conn,conn2,proc)) {
System.out.println(proc + ” is different in two
databases”);
}
} else {
System.out.println(“Procedure — ” + proc + ” NOT FOUND
IN db2″);
}
}

for (Iterator iter = proc2.iterator(); iter.hasNext();) {
String proc = (String) iter.next();
System.out.println(“ONLY IN DB= ” + proc);
}

} finally {
conn.close();
conn2.close();
}

}

/**
* @param conn
* @param conn2
* @param proc
*/
private static boolean compareDefinitions(Connection conn, Connection
conn2, String proc) throws Exception{
Statement st = conn.createStatement();
Statement st2 = conn2.createStatement();

ResultSet rs = st.executeQuery(“sp_helptext ” + proc);
ResultSet rs2 = st2.executeQuery(“sp_helptext ” + proc);

rs.next();
String st1 = rs.getString(“Text”);
rs2.next();
String str2 = rs2.getString(“Text”);

return st1.equals(str2);
}
}

Best Regards

Ravindra

Advertisements