SQL DB2：如何查找表或表列表中是否存在列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8743810/
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: How do I find if a column is present in a table or list of tables?
提问by Mo.
Im using a DB2 Database. What would be a suitable SQL query to find out if a column is exists in a table or list of tables?
我使用 DB2 数据库。找出表或表列表中是否存在列的合适 SQL 查询是什么？
e.g
例如
if "column_name" is found in "table name" or [list of table names]
return true or the name of tables that have that column.
Many thanks.
非常感谢。
采纳答案by Micha? Powaga
Use SYSCAT.COLUMNScatalog view:
SELECT TABNAME
FROM SYSCAT.COLUMNS
WHERE
TABNAME IN ('table name 1', 'table name 2') AND
COLNAME = 'column_name';
回答by bhamby
Tested on DB2 z/OS 9.1 and LUW 9.7:
在 DB2 z/OS 9.1 和 LUW 9.7 上测试：
SELECT STRIP(TBCREATOR) || '.' || STRIP(TBNAME)
FROM SYSIBM.SYSCOLUMNS
WHERE NAME = 'your_col'
AND TBNAME IN ('list', 'of', 'tables')
If you only want results from a specific schema you might add AND TBCREATOR = 'your_schema'to the end of the query.
如果您只想要来自特定模式的结果，您可以添加AND TBCREATOR = 'your_schema'到查询的末尾。
回答by Micha? Powaga
Another way to do this is with error handling:
另一种方法是使用错误处理：
declare v_sql varchar(1000);
declare col_missing integer default 0;
declare col_does_not_exist condition for sqlstate '42703';
declare continue handler for col_does_not_exist set col_missing = 1;
set v_sql = 'select table.foo from table';
execute immediate v_sql;
if col_missing = 1 then
--Do something if column foo doesn't exist.
end if;
This method will work on session tables, and you can also use it on an object even if you don't know whether it is a table, view, alias, etc.
这个方法对会话表有效，你也可以在对象上使用它，即使你不知道它是不是表、视图、别名等。
It is important to specify table.foorather than just the column name, as otherwise the existence of another object (such as a variable) called foowould break the test.
重要的是指定table.foo而不仅仅是列名，否则调用的另一个对象（例如变量）的存在foo会破坏测试。
This continue handler will mask any other errors for columns missing within the scope, so it is best to limit the scope to just the test you want to do.
此继续处理程序将掩盖范围内缺少的列的任何其他错误，因此最好将范围限制为您想要执行的测试。
