Crawling all SQL Server tables and columns for a specific value

We had a quick need to search all columns in every table during a data discovery exercise. This is by no means the most efficient, but it does work.

It pulls 10,000 rows at a time from each table, and parses each row for all columns and checks the value for a particular string.

It would be trivial to multi thread this by pushing the tables onto a stack and having multiple threads pop them off for processing. The largest table would be the overall bottleneck.

import java.sql.*;

//c:\>set classpath=c:\sqljdbc4.jar;.
//c:\>javac workedHours.java
//c:\>java -Djava.library.path="C:\Users\showard\Downloads\sqljdbc_4.0\enu\auth\x64" workedHours
//create table sensordata (sitename varchar2(100),siteid varchar2(100),servername varchar2(100),serverdate timestamp,createdate timestamp,valuea number,valueb number) tablespace expanly_data;

public class wbTables {
  public static void main (String args[]) throws Exception {
    Connection conn = DriverManager.getConnection("jdbc:sqlserver://host:1433;databaseName=dbname;integratedSecurity=true;");
    String s = "select quotename(schema_name(sobj.schema_id)) + '.' + quotename(sobj.name) as [tablename], sum(sdmvptns.row_count) as [rowcount] " +
                 "from sys.objects as sobj inner join sys.dm_db_partition_stats as sdmvptns on sobj.object_id = sdmvptns.object_id " +
                 "where sobj.type = 'u' and sobj.is_ms_shipped = 0x0 and sdmvptns.index_id < 2 " +
                 "group by sobj.schema_id,sobj.name " +
                 "having sum(sdmvptns.row_count) > 0";
    ResultSet rst = conn.createStatement().executeQuery(s);
    while (rst.next()) {
      Statement stm = conn.createStatement();
      stm.setFetchSize(10000);
      ResultSet rst2 = stm.executeQuery("select * from " + rst.getString(1) + " with (nolock)");
      ResultSetMetaData rsmd = rst2.getMetaData();
      System.out.println(rst.getString(1) + " " + new java.util.Date());
      while (rst2.next()) {
   	for (int i = 1; i <= rsmd.getColumnCount(); i++) {
          if (rst2.getString(i) != null && rst2.getString(i).equals("2071")) {
            System.out.println(rsmd.getColumnName(i) + " " + rst2.getString(i));
          }
        }
      }
      rst2.close();
      stm.close();
    }
  }
}