在 DB2 for i 上，搜索列，以列表形式返回表名 [英] On DB2 for i, Search for Column, return table names in list form

问题描述

我还是个菜鸟，如果这个问题有点明显，请原谅.我确实搜索了答案，但要么无法理解我找到的答案是如何应用的，要么就是找不到答案.

I'm still a bit of a noob, so pardon if this question is a bit obvious. I did search for an answer but either couldn't understand how the answers I found applied, or simply couldn't find an answer.

我在 DB2 for i 服务器上有一个庞大的数据库，我通过 SQLExplorer(基于 Squirrel SQL)使用 SQL 访问该数据库.这些表格的文档记录很差，首要任务是弄清楚如何找到我的方法.

I have a massive database housed on a DB2 for i server which I'm accessing using SQL through SQLExplorer (based on Squirrel SQL). The tables are very poorly documented and the first order of business is figuring out how to find my way around.

我想写一个这样的简单查询:

I want to write a simple query that does this:

1) 允许我搜索整个数据库以查找包含名为备注"的列(包含字段描述)的表.

1) Allows me to search the entire database looking for tables that include a column called "Remarks" (which contains field descriptions).

2) 然后我希望它在该列中搜索关键字.

2) I then want it to search that column for a keyword.

3) 我想要返回一个表，其中包含包含该关键字的表的名称(只是名称，我可以稍后按字母顺序查找表并查看如果需要，请在里面.)

3) I want a table returned that includes the names of the tables that include that keyword (just the name, I can look up the table alphabetically later and look inside if I need to.)

我需要这个搜索是超轻量级的，我希望我描述的概念能够实现这一点.任何占用大量资源的东西都可能会激怒服务器的系统管理员.

I need this search to be super lightweight, and I'm hoping the concept I describe will achieve that. Anything that eats up a lot of resources will likely anger the sys admin for the server.

只是为了表明我已经尝试过(而且我是一个完全的菜鸟)，这就是我到目前为止所得到的.

Just to show I have tried (and that I am a complete noob), here's what I've got so far.

SELECT *
FROM <dbname>
WHERE Remarks LIKE '<keyword>'

随意嘲笑，我告诉过你我是个白痴:-).

Feel free to mock, I told you I'm an idiot :-).

有什么帮助吗?也许至少是朝着正确的方向推进?

Any help? Perhaps at least a push in the right direction?

PS - 我似乎无法在 SQLExplorer 中找到搜索功能，如果有人知道我是否可以使用简单的搜索或过滤器来实现相同的目标...那就太好了.

PS - I can't seem to find a search function in SQLExplorer, if someone knows if I can perhaps use a simple search or filter to accomplish this same goal...that would be great.

推荐答案

可以通过查询系统目录来识别表:

You can query the system catalog to identify the tables:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME 
FROM QSYS2.SYSCOLUMNS WHERE UPPER(DBILFL) = 'REMARKS'

然后分别查询每个表:

SELECT * FROM TABLE_SCHEMA.TABLE_NAME WHERE Remarks LIKE '%<keyword>%'

有关详细信息，请参阅 LIKE 谓词模式表达式.

See the LIKE predicate for details of the pattern expression.

这篇关于在 DB2 for i 上，搜索列，以列表形式返回表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！