Is there anyway in sql to do a search for both the schema/table/column name when all you know is the field?
For the life of me i can’t find where a value is stored. In a different platform for different users the data is available readily. On an individual basis, but for many, many, many records i need to use sql to pull this value for very many policies, and i can’t find where it’s stored.
so really, what i’m wondering is if there’s a sytax that says "in _____ schema, what table and column contains “123456789987654321” ?
Any ideas? Yes, i suspect it would take a long time to find. I do have some narrowing characteristics, like, "identifier= ‘abcdefgh’ " so the search would be much less broad than casting the largest net possible.
So you know an example of the data you are looking for, and want to find the schema/table/column that has that data?
Do you know the data type?
My big stupid suggestion: The first thing (presuming you know the database) is to get a list of all schema/table/columns. I don’t know the command that gives a big list like that. If there aren’t 1000 tables in the DB, you could script each table create to clipboard and paste into Excel for easier manipulation. From there narrow down to those columns of your data type. You could use Excel to do a huge query that looks for your value in every column.
I’m not sure if this works in all RDBMS, nor if there are permissions issues you’d have to work out with DBAs, but in Oracle DBs I’ve used there is a “PUBLIC” schema with tables like “ALL_TABLES” or “ALL_TABLE_COLUMNS” that have ‘queryable’ metadata.
Thinking about my stupid way (MS-SQL), there are what I think of as “meta-tables” that run the DB. When you right click on a table and choose script as drop and create to query, the query checks to see whether that table exists. If you do that to a random table, you should be able to find the meta-data table name and how to query it to get the list of tables. Doesn’t give you the field names, but there might be more info you can track down (even without using help!)
ETA:
MS-SQL uses a table called information_schema.columns that looks like it has what you need to create the big stupid query. You might try SELECT DISTINCT [fieldname n] FROM [table m] WHERE [fieldname n] = [known value]. That will return at most just one line per line of query.
There should be some metadata tables, which gives you information on table structure. It may have a name like information_schema, depending on your platform, where you can get a list of all tables, columns and datatypes. This would also depend on you having read access to the metadata tables.
I’m guessing you’re looking for id numbers, which ideally would be stored in a char or varchar field (it may also be stored in a numeric field, which is not good practice if there’s a chance of leading zeros). If you can get a list of the char/varchar fields, it should also give you the maximum number of characters allowed in the field. You can then exclude the shorter character columns. This will at least narrow down the number of columns you need to wade through.
If the column names give you no clues, you could do a partial string match for each of the columns in the table (with an OR between each condition), as long as the table isn’t too big. If the table is big you might want to return a distinct sample of say, 1000 rows, from each of the qualifying columns for each table to see which columns might be more likely to contain your string.
Do you have a DB admin you can consult? Both for advice and permission. I’ve seen people crash an entire server more than once trying to do stuff like that (or at least, make it nonfunctional).