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();
}
}
}