h a l f b a k e r yBite me.
add, search, annotate, link, view, overview, recent, by name, random
news, help, about, links, report a problem
browse anonymously,
or get an account
and write.
register,
|
|
|
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.
Please log in.
If you're not logged in,
you can see what this page
looks like, but you will
not be able to add anything.
Annotation:
|
| |
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. |
|
| |
"get 99% of programmers to reinvent exactly the same things" 2.0 |
|
| |
Errm... you've got me there, [Toaster]; ALL WHAT? |
|
| |
umm... thought there might be an "ALL" parameter for table-name. (don't recall) or * wildcard. |
|
| |
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. |
|
| |
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 |
|
| |
Visual Studio auto-completes names which does improve the situation somewhat. |
|
| |
//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. |
|
| |