How can I find specific records in a large database if...?

How can I find specific records in a large database if...?
I do not have access to any DB schema.
I do not know even which (1000) table(s) to choose.
I do not know what any of the relationships are in the tables.

"There used to be this great tool called SQL Grep that you could plug in any value integer, string, date, boolean etc.
The tool would search a large database in a few minutes identifying and determining what Table and Field the record is in.
Thus giving the ability to reverse engineer the tables and find all the relationships but that products owner shut all the products down closing the entire company.
Not sure why."

Is there a tool that can do this. This question was asked 7 years ago but the only tool was SQL Grep at the time.
I do not even know what classification this product is or what to search Google for? Would it be called a data search tool?
No idea...
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Olaf DoschkeSoftware DeveloperCommented:
But you can make a connection to the database, can't you?
What product are we talking about? Oracle (PLSQL hints on that)

You can always read a schema.

plsql offers INFORMATION_SCHEMA tables with data about the tables, go for https://docs.oracle.com/cd/E19078-01/mysql/mysql-refman-5.0/information-schema.html

If I'd get aware of anybody using tools scanning all tables and fields for certain values, I'd kill them. This is taking resources off the server, that should server normal users, this is doing things in the most lazy way having a high chance misleading you into some finding merely a hit by chance to something unrelated, perhaps even just a log table.

The first thing to do about a database you don't know is getting to know it. And your customer doesn't have any diagrams or this is about a proprietary database of a software not maintained by them and you still want to extend features on your own instead of going to the software vendor for it, then get your grips and query schema data, it's always available.

You don't act on a database as if it's just a blob of a mix of binary and text in one file you open in notepad and search it. That's simply total nonsense and developers of such tools should just be taken aside, and shoot, too.

Grep is a fine concept of Linux to go through text files and more or less intelligently parse them, but that's not how databases are composed, that's the totally wrong approach. I know guys like you, coming from consultant firms aiming for a consolidation and migration to SAP or some other ERP and trying to understand current application and data landcape. I've seen expensive people, only knowing perl. The literal guy only having a hammer and every problem can only be solved by being a nail or being transformed to a nail. I can't belive you don't even know you can get schematic data from any database.

Even if you just get a list of tables, and search for the term you're searching for, eg seeing a product code displayed in a software form, you want to find out where that's stored, the table list will likely have a products table, that's your first choice of searching. And information schema will also tell you the available columns.

If you can't think of anything else but grepping through all data, then do so, but don't load the server with such an approach. Export all data into csv files and grep them, if you can't cope with databases.

Bye, Olaf.
1
Geert GOracle dbaCommented:
in a few minutes ?
it takes that long to throw an error ?

if you don't have access to any db schema, then you can't read anything

ask a co-worker for access ... that might help
0
slightwv (䄆 Netminder) Commented:
Is this not a duplicate of:
https://www.experts-exchange.com/questions/29087423/PLSQL-views-that-can-help-identify-a-specific-record-the-type-the-field-and-table-names-in-a-large-Oracle-DB-With-NO-Schema-or-knowledge-of-which-table-to-start-with.html

>>plsql offers INFORMATION_SCHEMA tables with data about the tables, go for

That is MYSQL not Oracle.

Also PL/SQL is Oracle's Procedural Language for building stored procedures and functions.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Olaf DoschkeSoftware DeveloperCommented:
Please excuse the tone of what I said yesterday, clearly a rant. But I'm surely not the only database developer having that opinion about such solution approaches, for example, see here: https://www.experts-exchange.com/questions/21590450/grep-through-SQL-DB.html#a15057838

Also perhaps a hint on why a company offering such tools sooner or later closes down.

#1 EE Expert Guy Hengel [angelIII / a3] calls such a tool...
...a good performance and security killer.

While you may only wait "a few minutes" this means you hog the server for that time, and may cause many users to have flaky or broken connections. Such things already can cause many costs. When a database gets slow or even disconnects users, because it's not dimensioned to support a full scan on all tables, just because you can do, means hourly loan rates times users affected.

>I do not have access to any DB schema.
Well, as soon as you can connect to a DB and have sufficient permissions to really do your job as a consultant or new developer in the team, you must be able to query INFORMATION_SCHEMA tables. This is the minimum you will need to do such work and a tool also will need that permission, so that doesn't unbind your customer to provide what you need.

If you need to document the database surely you're not there short-term, you'll need to familiarize with the database anyway. So do that.

Bye, Olaf.
0
Geert GOracle dbaCommented:
olaf, you're still ranting

INFORMATION_SCHEMA does not exist in oracle

it's dba_tables, dba_views, dba_tab_columns, dba_triggers, etc ...
0
Olaf DoschkeSoftware DeveloperCommented:
OK, sorry for that, I just was shortsighted about the documentation coming from docs.oracle.com. Anyway, you have your tables/views of meta data. In MSSQL you have sys views about dbs, tables, columns etc. AND Information_Schema views, I faintly remember something in Oracle in that name schema, too, but I haven't done oracle for a long time.

Bye, Olaf.
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
Dear Olaf,
Thank you for the education. I am very coachable and can either lead or follow on command. Something I learned long ago. My problem is so many companies usually smaller to mid sized have this problem of an application whereby the software manufacturer does not allow access to the proprietary "information schema" and try to impose their team on a project. Anywhere from $1500-$2500 a day is not unusual. Some consultants staying for months. To a small business or even mid-sized can put a serious strain on a budget. That is why so many clients go overseas to source foreign labor. I found a niche with the SQL Grep where with my limited knowledge at the time of SQL I could query a db quickly. Usually after office hours never impacting performance. This giving a lower cost solution to clients keeping me employed in my own country which is nice when you are too old to dig ditches or shovel manure as I did growing up on a farm. So to answer some of the questions:

But you can make a connection to the database, can't you? I can make a connection to the live database. I am working with Tableau so I have to be careful during the day because it is in use and I need to make extracts once I find the correct tables.
What product are we talking about? Oracle (PLSQL hints on that) It is Oracle DB utilizing PLSQL. The product is a cost analysis and estimating tool that is used in the housing industry.

You can always read a schema. There are four tables that have SCHEMA in them and they have nothing but settings information in them looks like they are used for export.

plsql offers INFORMATION_SCHEMA tables with data about the tables, go for https://docs.oracle.com/cd/E19078-01/mysql/mysql-refman-5.0/information-schema.html    I could not find any tables that have the INFORMATION_SCHEMA in them? Oh I wish I had.

NETMINDER unfortunately no it is not a duplicate. I was asking there if anyone knows of a product out there like SQL Grep that does what it did. Which helps a guy like me not have to spend hours hunting for a table and field out of 1000's of tables for a record that is unique. I would not say I am lazy just inexperienced and Grep helped me keep costs and time low for customers. It was great going to an application and look at a record in the GUI and say where is that located. Click a button and say ' Hell there it is then figure out how to connect the dots? Maybe there is a super easy way to search an entire database in PLSQL like....feel free to correct me....
Select *
From All Tables
Where All String Fields that contain "12345678"
as an example

 Since there are so many proprietary products out there with poor designs and no documentation, table definitions or linking diagrams. It really made that need easy to attain. However if someone can show me how to create that in PLSQL I am all ears???
0
slightwv (䄆 Netminder) Commented:
>>I could not find any tables that have the INFORMATION_SCHEMA in them? Oh I wish I had.

It has already been posted:  That is MySQL.  Oracle has its own data dictionary views and Geert posted them.

>>unfortunately no it is not a duplicate.

But you are looking for the same thing in both questions:  How to find a value in a column without knowing anything about the database.

You even referenced a question from one of my old comments.

If the other question is just how to reverse engineer a database, I posted that:  SQL Developer Data Modeler.

>>There are four tables that have SCHEMA in them and they have nothing but settings information in them looks like they are used for export.

I'm not sure we are using the same terms.  In Oracle a schema is pretty much a user that owns objects.  A table is an object.

What do you mean when you say a table has a schema?

>>to search an entire database in PLSQL like....

As I mentioned PL/SQL is a Procedural Language.  What you posted is just SQL.  No fancy names.

and no, it isn't that simple.

>>f someone can show me how to create that in PLSQL I am all ears

It might not require PL/SQL.  There are some tricks to do it with straight SQL.

Remember, it is horribly inefficient no matter what option you choose.

Here is one I came up with:
Query all tables of Oracle database: return all records that hold string
https://www.experts-exchange.com/questions/29077933/Query-all-tables-of-Oracle-database-return-all-records-that-hold-string.html#a42433223

It build on this one:
identifying columns in all the tables with a specific value
https://www.experts-exchange.com/questions/28069987/identifying-columns-in-all-the-tables-with-a-specific-value.html#a39001854
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Olaf DoschkeSoftware DeveloperCommented:
You still don't get it.

whereby the software manufacturer does not allow access to the proprietary "information schema" and try to impose their team on a project.
Are you saying you tried SELECT * FROM dba_tables and didn't get the list of tables?

If this doesn't work out, you'll also not be able to generate SELECT * FROM xyz, there is no SQL that queries ananymously, you need the names of tables you want to query anyway.

You don't search for "123456" as example, you search for a specific value you get displayed from this software as account number, product code, so you know what you actually search for, don't you? And developers store data in tables and columns named in an understandable manner, normally. Unless you tell me this software uses obfuscation and names it's tables and columns with cryptic alphanumeric names without any meaning.

All I'm saying is, going about the topic of determining the schema you miss by querying the schema meta data is a much more effective way and you may already know many table and column meanings before probing them to contain expected values. You're going bottom up on the problem instead of top down, that's always last resort and not first choice.

And again, it's really not just unfortunate to need to search that way, it's rude AND you don't need to search the way you intend, because you CAN get at the schema data you think is hidden. No Oracle database will have no meta data, it's always there. And if that database IS hosted at your customer you WILL be able to get the dba permission to query dba_tables and all the other tables containing the core information you seek, there might even be comments/descriptions.

Bye, Olaf.
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
I got a lot of clarity in those statements so thank you very much for all the expertise and help. I now understand what PLSQL means. I always thought it was more of a brand of SQL. Procedural Language Stored Procedures and functions. I do actually know what a stored procedure is. I typically work on the read only side of the data for reports and visualizations and have been wanting to learn more about the database side of things too help clients who do not have the staff for it. I typically only have to do small simple things and I see the need for more knowledge.....don't we all need more knowledge. I wish I had the brains to cure cancer right and a few other diseases right now.

So with that said thank you very much for all this help. If you have any final advice on where I might go to learn as much more about database manipulation or where I might start? I know how to pull a record from a single table, filter, sort and simple stuff like that. Any programs courses that might help get me up to speed faster?
0
slightwv (䄆 Netminder) Commented:
>>Any programs courses that might help get me up to speed faster?

The old system paradigm:  Good, Fast, Cheap.  Pick two.  You will NEVER get all three!

Fast and Good (well, decent): Oracle Education.  Learning Tree is OK as well.

Cheap and Good:  Oracle Documentation.  If you are new, start with the Architecture Guide.

Cheap and Fast:  I really have nothing for this combo since I would never recommend it.
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.