Browse DevX
Sign up for e-mail newsletters from DevX


Home-grown Data Mining in SQL Server-3 : Page 3




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Immediate Solution vs. Better Judgment
The first thing that sprang to mind was trying to use the undocumented system stored procedures, sp_MSForEachDB and sp_MSForEachTable, to query each table in each database on a particular server. I'm not sure whether this approach would have worked well. While a query both for metadata (domains, data types, etc.) and for the data in a table is possible, I couldn't figure out how to limit the return values by using the TOP modifier in the select statement. TOP would have prevented the query from returning potentially millions of rows. After all, I knew what I was looking for but didn't know how many records my statement could potentially return, so I needed some way to limit the result. I ended up abandoning that approach, but clearly I could use a fragment of the idea. I could use the information schema views to restrict my investigation at least by data type.

I crossed over to "the dark side" when, instead of taking the time to design a set-based solution for the problem, I considered applying nested cursors to manage the search. Nested cursors matched my original undocumented system stored procedure solution. I let the logical equivalent of the solution override my better judgment. As it turned out, a set-based solution wasn't all that critical, because the cursor solution was only slightly slower—and I needed the answer as soon as I could get it.

My strategy was to create an outer loop that would supply the table name, and then open an inner loop that would supply the column name. The inner loop would be restricted by an IN clause and a data type qualifier. I planned to build a string out of the two loops and then execute the string. If necessary, I could execute the string into a table so the results could be reviewed after the run, but in the solution's first incarnation I was happy just to learn the names of the database, table, and column that contained my four values. I could get away with this because I was looking for character data. I didn't know if it was char, nchar, varchar, or nvarchar, but at least I could eliminate the other twenty or so datatypes available to SQL Server.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date