A normal SQL query says, politely, "show me these columns from these tables meeting these criteria."

I propose an extension to SQL, by which you can say, less politely, "I know there's something in this database somewhere about, say 'Tono-Bungay'. No, I don't know which table; the contractor who created the schema (using vowel-free abbreviations of the original Serbo-Croat) left three years ago without doco. Yes, I've got all night if necessary. You tell me which table(s) and columns(s)."

This can already be done in a roundabout way if you write a script which queries the tables where the schema is defined, creates a list of all the text columns and then runs SELECT statements against them ad nauseam. I'm just proposing that SQL itself should support it directly. I'm imagining something along the lines of
EXTRACTBRUTE <search-string> {output-spec}
{output-spec} ::= TABLENAME | COLUMNNAME | PKVALUES

This would be helpful for stalkers, hackers, reverse-engineers and Business Intelligence consultants.
-- pertinax, Oct 26 2009

Hmmm, interesting - I could have done with something like this the other week - but just did it eventually by running a select * of the whole table into a text editor and then doing a find on the results.

If I knew how the database stored its files, I might consider looking in them with a Hex editor - but I'm not sure that would help either.

But your roundabout method isn't so roundabout - you could create a super-normalising stored procedure that interrogates the database schema, extracts the contents of each into a (for convenience) delimited, concatenated string (perhaps headed by a column sporting the table name or other schema-details that this row was lifted from) that gets stored into a table you've reserved for this particular usage, and then performs a substring search (slow, but you did say you had all night - although, this might take a few nights, and a fair amount of tablespace) from the resulting single-column super-normalised table for the pattern you are looking for. Any hits would return rows containing the original data in context, as well as a table heading showing where in the schema it came from.

Oracle's really good for this, but I'm sure other databases can be equally self-referential.
-- zen_tom, Oct 26 2009


"get 99% of programmers to reinvent exactly the same things" 2.0
-- Jinbish, Oct 26 2009


"ALL"
-- FlyingToaster, Oct 26 2009


Errm... you've got me there, [Toaster]; ALL WHAT?
-- pertinax, Oct 26 2009


umm... thought there might be an "ALL" parameter for table-name. (don't recall) or * wildcard.
-- FlyingToaster, Oct 26 2009


You can use '*' in the SELECT clause, but not, I think, in the WHERE clause. As for ALL, perhaps you're thinking of the family of Oracle views such as ALL_TABLES, ALL_TAB_COLUMNS, etc.; these are, of course, very useful, but querying them tells you nothing about what's *in* the tables and columns.
-- pertinax, Oct 26 2009


Most systems have a dump facility what prepares a plaintext export of the whole database. Dump -> Search -> Backward-search to table definition line -> Backward-search to database definition line
-- vincevincevince, Oct 26 2009


Visual Studio auto-completes names which does improve the situation somewhat.
-- theircompetitor, Oct 27 2009


//Most systems have a dump facility//

Well, yes, and you could also use such a dump for implementing ordinary SELECT statements, but I'd rather not.
-- pertinax, Oct 27 2009



random, halfbakery