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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
\r\nimport java.sql.*;\r\n\r\n\/\/c:\\>set classpath=c:\\sqljdbc4.jar;.\r\n\/\/c:\\>javac workedHours.java\r\n\/\/c:\\>java -Djava.library.path=\"C:\\Users\\showard\\Downloads\\sqljdbc_4.0\\enu\\auth\\x64\" workedHours\r\n\/\/create table sensordata (sitename varchar2(100),siteid varchar2(100),servername varchar2(100),serverdate timestamp,createdate timestamp,valuea number,valueb number) tablespace expanly_data;\r\n\r\npublic class wbTables {\r\n public static void main (String args[]) throws Exception {\r\n Connection conn = DriverManager.getConnection(\"jdbc:sqlserver:\/\/host:1433;databaseName=dbname;integratedSecurity=true;\");\r\n String s = \"select quotename(schema_name(sobj.schema_id)) + '.' + quotename(sobj.name) as [tablename], sum(sdmvptns.row_count) as [rowcount] \" +\r\n \"from sys.objects as sobj inner join sys.dm_db_partition_stats as sdmvptns on sobj.object_id = sdmvptns.object_id \" +\r\n \"where sobj.type = 'u' and sobj.is_ms_shipped = 0x0 and sdmvptns.index_id < 2 \" +\r\n \"group by sobj.schema_id,sobj.name \" +\r\n \"having sum(sdmvptns.row_count) > 0\";\r\n ResultSet rst = conn.createStatement().executeQuery(s);\r\n while (rst.next()) {\r\n Statement stm = conn.createStatement();\r\n stm.setFetchSize(10000);\r\n ResultSet rst2 = stm.executeQuery(\"select * from \" + rst.getString(1) + \" with (nolock)\");\r\n ResultSetMetaData rsmd = rst2.getMetaData();\r\n System.out.println(rst.getString(1) + \" \" + new java.util.Date());\r\n while (rst2.next()) {\r\n \tfor (int i = 1; i <= rsmd.getColumnCount(); i++) {\r\n if (rst2.getString(i) != null && rst2.getString(i).equals(\"2071\")) {\r\n System.out.println(rsmd.getColumnName(i) + \" \" + rst2.getString(i));\r\n }\r\n }\r\n }\r\n rst2.close();\r\n stm.close();\r\n }\r\n }\r\n}\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"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…<\/p>\n