SQL DB2 查询以检索给定模式的所有表名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3583963/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
DB2 Query to retrieve all table names for a given schema
提问by Ben
I'm just looking for a simple query to select all the table names for a given schema.
我只是在寻找一个简单的查询来选择给定模式的所有表名。
For example, our DB has over 100 tables and I need to find any table that contains the sub-string “CUR”. I can use the like command once I have all the tables.
例如，我们的数据库有 100 多个表，我需要找到任何包含子字符串“CUR”的表。一旦我拥有所有表，我就可以使用 like 命令。
回答by paxdiablo
select * from sysibm.systables
where owner = 'SCHEMA'
and name like '%CUR%'
and type = 'T';
This will give you all the tables with CURin them in the SCHEMAschema.
这将为您提供架构CUR中的所有表SCHEMA。
See herefor more details on the SYSIBM.SYSTABLEStable. If you have a look at the navigation pane on the left, you can get all sorts of wonderful DB2 metatdata.
有关该表的更多详细信息，请参见此处SYSIBM.SYSTABLES。如果您查看左侧的导航窗格，您可以获得各种精彩的 DB2 元数据。
Note that this link is for the mainframe DB2/z. DB2/LUW (the Linux/UNIX/Windows one) has slightly different columns. For that, I believe you want the CREATORcolumn.
请注意，此链接适用于大型机 DB2/z。DB2/LUW（Linux/UNIX/Windows 之一）的列略有不同。为此，我相信您想要该CREATOR专栏。
In any case, you should examine the IBM docs for your specific variant. The table name almost certainly won'tchange however, so just look up SYSIBM.SYSTABLESfor the details.
在任何情况下，您都应该检查 IBM 文档以了解您的特定变体。然而，表名几乎肯定不会改变，所以只需查找SYSIBM.SYSTABLES详细信息。
回答by deepthi
DB2 LIST TABLES FOR SCHEMA <schema_name>
回答by Bert
On my iSeries I have to run this command from iNavigator:
在我的 iSeries 上，我必须从 iNavigator 运行此命令：
select *
from QSYS2.SYSTABLES
where TABLE_SCHEMA
like 'SCHEMA_NAME'
and TYPE = 'T';
回答by KBO
You should try this:
你应该试试这个：
select TABNAME from syscat.tables where tabschema = 'yourschemaname'";
回答by schneiti
Using the DB2 commands (no SQL) there is the possibility of executing
使用 DB2 命令（无 SQL）有可能执行
db2 LIST TABLES FOR ALL
This shows all the tables in all the schemas in the database.
这显示了数据库中所有模式中的所有表。
回答by Paul Vernon
For Db2 for Linux, Unix and Windows(i.e. Db2 LUW) or for Db2 Warehouseuse the SYSCAT.TABLEScatalog view. E.g.
对于Db2 for Linux、Unix 和 Windows（即 Db2 LUW）或Db2 Warehouse，请使用SYSCAT.TABLES目录视图。例如
SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA LIKE '%CUR%' AND TYPE = 'T'
Which is a SQL statement that will return all standardtables in all schema that contains the substring CUR. From a Db2 command line you could also use a CLP command e.g. db2 list tables for all | grep CURto similar effect
这是一条 SQL 语句，它将返回包含 substring 的所有模式中的所有标准表CUR。从 Db2 命令行，您还可以使用 CLP 命令，例如db2 list tables for all | grep CUR达到类似效果
This pagedescribes the columns in SYSCAT.TABLESincluding the different values for the TYPEcolumn.
此页面描述了SYSCAT.TABLES中的列，包括该列的不同值TYPE。
A = Alias
G = Created temporary table
H = Hierarchy table
L = Detached table
N = Nickname
S = Materialized query table
T = Table (untyped)
U = Typed table
V = View (untyped)
W = Typed view
Other commonly used catalog views incude
其他常用的目录视图包括
SYSCAT.COLUMNS Lists the columns in each table, view and nickname
SYSCAT.VIEWS Full SQL text for view and materialized query tables
SYSCAT.KEYCOLUSE Column that are in PK, FK or Uniuqe constraints
In Db2 LUW it is considered bad practice to use the SYSIBMcatalog tables (which the SYSCATcatalog views select thier data from). They are less consistent as far as column names go, are not quite as easy to use, are not documented and are more likely to change between versions.
在 Db2 LUW 中，使用SYSIBM目录表（SYSCAT目录视图从中选择数据）被认为是不好的做法。就列名而言，它们不太一致，不太容易使用，没有记录，并且更可能在版本之间发生变化。
This page has a list of all the catalog views Road map to the catalog views
此页面包含所有目录视图的列表 目录视图的路线图
For Db2 for z/OS, use SYSIBM.TABLESwhich is described here. E.g.
对于Db2 for z/OS，请使用此处SYSIBM.TABLES描述的。例如
SELECT CREATOR, NAME FROM SYSIBM.SYSTABLES WHERE OWNER LIKE '%CUR%' AND TYPE = 'T'
For Db2 for i(i.e. iSeries aka AS/400) use QSYS2.SYSTABLESwhich is described here
对于Db2 for i（即 iSeries aka AS/400）使用QSYS2.SYSTABLES，此处描述
SELECT TABLE_OWNER, TABLE_NAME FROM QSYS2.SYSTABLES WHERE TABLE_SCHEMA LIKE '%CUR%' AND TABLE_TYPE = 'T'
For DB2 Server for VSE and VMuse SYSTEM.SYSCATALOGwhich is described here DB2 Server for VSE and VM SQL Reference
对于DB2 Server for VSE 和 VM 的使用SYSTEM.SYSCATALOG，此处描述了DB2 Server for VSE 和 VM SQL 参考
SELECT CREATOR, TNAME FROM SYSTEM.SYSCATALOG WHERE TABLETYPE = 'R'
回答by Cristian
db2 connect to MY_INSTACE_DB with myuser -- connect to db2
db2 "select TABNAME from syscat.tables where tabschema = 'mySchema' with ur"
db2 terminate -- end connection
回答by federico molteni
select name from sysibm.systables
where name like '%ISP%'
and type = 'T'
回答by BharathRapolu
You can also get the table names simply by typing LIST TABLES in DB2
您还可以通过在 DB2 中输入 LIST TABLES 来获取表名
回答by Fuangwith S.
This should work:
这应该有效：
select * from syscat.tables
