我只是想要一个简单的查询语句来选择给定schema下的所有表名。
例如,我们的数据库有100多个表,我需要找到包含子字符串"CUR"的任何表。一旦我得到所有表的名称,我可以使用like命令。
我只是想要一个简单的查询语句来选择给定schema下的所有表名。
例如,我们的数据库有100多个表,我需要找到包含子字符串"CUR"的任何表。一旦我得到所有表的名称,我可以使用like命令。
--for DB2/z
select * from sysibm.systables
where owner = 'SCHEMA'
and name like '%CUR%'
and type = 'T';
--for DB2/LUW
select * from sysibm.systables
where CREATOR = 'SCHEMA'
and name like '%CUR%'
and type = 'T';
SCHEMA
模式中所有包含CUR
的表。此处有关于SYSIBM.SYSTABLES
表的更多详细信息。如果您查看左侧的导航窗格,可以获得各种精彩的DB2元数据。DB2 LIST TABLES FOR SCHEMA <schema_name>
select *
from QSYS2.SYSTABLES
where TABLE_SCHEMA
like 'SCHEMA_NAME'
and TYPE = 'T';
你应该尝试这个:
select TABNAME from syscat.tables where tabschema = 'yourschemaname'";
db2 "list tables for all" > tables.txt
- bonhSELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA LIKE '%CUR%' AND TYPE = 'T'
db2 list tables for all | grep CUR
达到类似的效果。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
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 Unique constraints
SYSIBM
目录表被认为是一种不良的做法(SYSCAT
目录视图从中选择数据)。它们的列名不够一致,不太容易使用,没有文档说明,并且在版本之间更可能发生变化。SYSIBM.TABLES
。例如:SELECT CREATOR, NAME FROM SYSIBM.SYSTABLES WHERE OWNER LIKE '%CUR%' AND TYPE = 'T'
QSYS2.SYSTABLES
,该表在此处有描述。SELECT TABLE_OWNER, TABLE_NAME FROM QSYS2.SYSTABLES WHERE TABLE_SCHEMA LIKE '%CUR%' AND TABLE_TYPE = 'T'
SYSTEM.SYSCATALOG
DB2 Server for VSE and VM SQL 参考。SELECT CREATOR, TNAME FROM SYSTEM.SYSCATALOG WHERE TABLETYPE = 'R'
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
select name from sysibm.systables
where name like '%ISP%'
and type = 'T'
这是我的工作方案:
select tabname as table_name
from syscat.tables
where tabschema = 'schema_name' -- put schema name here
and type = 'T'
order by tabname
你也可以通过在DB2中键入LIST TABLES来简单地获取表名
systables
。 - paxdiabloSELECT * FROM qsys2.systables
- Anthony Rolfe