Question Search all columns of all tables

M

Mark2457

New Member

• Jun 9, 2015

• #1

Hi All

in MS SQL, I can search all columns for a given value (e.g. %test%). Can I do the same with Progress (using SQL)?

Regards

mark

 

TheMadDBA

Active Member

• Jun 9, 2015

• #2

From a SQL connection you can just use LIKE

From the 4GL you would use MATCHES

https://community.progress.com/comm...2352.openedge-11-5-product-documentation.aspx

 

M

Mark2457

New Member

• Jun 9, 2015

• #3

I know how to write the SQL and have ONLY SQL access to the DB (via Linked server), but I'm looking for a way to get a list of all the tables dynamically and search all fields. I can do this with regular SQL DB, but not linked server (that I'm aware of)

 

TheMadDBA

Active Member

• Jun 9, 2015

• #4

It wasn't clear in the original post what you were asking for.

If you want to get a list of all tables and columns you can look at _File and _Field. Whether it will work or not depends on your level of access to the VST tables.

 

Rob Fitzpatrick

ProgressTalk.com Sponsor

• Jun 10, 2015

• #5

Do you want to find the names of all fields in all tables or do you want to query every field (presumably every character field) in every table for a given string value?

Can you provide a precise definition of "search all fields"? I don't know what you mean by that.

 

G

GregTomkins

Active Member

• Jun 15, 2015

• #6

I think he means he wants to search all the (character, presumably) columns in all the tables, for a particular value, without naming each table and field in the query. Like this:

http://stackoverflow.com/questions/...l-columns-without-specifying-all-column-names

It sounds like it's not a well understood or directly supported thing, in even 'regular' SQL. In ABL, we'd do this with dynamic queries.

... or, my personal favorite, dump everything to text and use 'grep'. Fine for one-off tasks, not very practical for ongoing work though.

 

TomBascom

Curmudgeon

• Jun 16, 2015

• #7

Actually it sounds like an interview question -- it has been popping up in various flavors and forums a lot lately.

 

TheMadDBA

Active Member

• Jun 16, 2015

• #8

Those are usually a bit more obvious but it is possible I suppose.

I wonder how many people actually get jobs based off googling interview questions/posting for answers. We usually destroyed those applicants when we asked general questions about past projects or asked for more detail on what they loved/hated about the 4GL.