Can I use a Database View to MERGE tables rather than JOIN tables?

[Geoffrey Bishop]

Hello.

I have Table A and Table B.  Both Table A and Table B have a "script" field.  I would like to be able to run a query on both the table A script field and the Table B script field all at once.  I was wondering if a Database View might help me do this.

In other words, I have A.script and B.script, and I want to search for an arbitrary string "xyz" in the script field of both of those tables at the very same time.  I don't know if the record I am searching for is in table A or table B.  I just want to find it, and I only want to do one search, not two searches.

Is it possible to use  Database Views to accomplish this?  Thank you.

[Tom Sienkiewicz]

Hi, it seems since DB views will give you all the required fields as part of the new DB view table, you would be able to run a simple query saying Field A Contains or Field B Contains. Not sure if that is exaclty what you're after.

Is there any correlation between record from table A and B? Not exactly sure of the use case here.

I would say just test it as creating a DB view doesn't take too long and see if this gets you the right kind of result...

[Geoffrey Bishop]

Hello, @Tomasz Sienkiewicz ,

I don't want the results going into 2 separate fields, because I might have 50 tables joined together in this way.  So I don't want to have to say "Is it in column A or column B or column C, or column D... etc."

The use case is I want to be able to search on all tables containing a script field.  I want to search for log message text in all the script fields of all the tables I specify.  And I want to do it in a simple UI.  I was hoping perhaps Database Views might be useful for this, but it's sounding like that won't work.  Thanks.  : (

[Tom Sienkiewicz]

Hi, I see your point.

If those are OOTB tables, you can always try to use either the Studio search, or search using sys_metadata.list and then search for a keyword. It will look in all of ServiceNow configuration records.

For custom tables, if they do not extend sys_metadata, I guess you'd need a simple UI - either a backend UI Page or a portal page/widget but you will still need to pass the names of the tables to search in. Perhaps those can be stored as a comma separated list in a sys_property.

Also thinking if perhaps it would be possible to configure the globals earch to work that way. You can check the search groups/settings of global search and experiment a bit.

[Geoffrey Bishop]

Global search rarely works for me because of the way it handles Underscore characters and other characters that it considers Punctuation.

Also, I have not had very much success with the Studio Search, even when searching in "all tables".

That's why I have arrived here with this question.  🙂

I have never tried searching sys_metadata.list.  I will have to look into that.