How to search for a colum name in DB2?

softwareengineer · New User Joined: 24 Aug 2005 Posts: 24 Location: Hyderabad

Suppose if i want to modify a column name and dont know how many tables have that column included. How can I search/find the column in all tables exist in DB2? using anything like SPUFI, SQL statements, etc...

Thanks in advance..

Karthikshankar · New User Joined: 29 Jun 2007 Posts: 2 Location: Pune

The Dictionary tables can be queried to get information about any columns. There are lots of dictionary tables like, SYSIBM.SYSTABLES, SYSIBM.SYSVIEWS, SYSIBM.SYSOBJECTS, SYSIBM.SYSPLANS etc. The Dictionary table SYSIBM.SYSCOLUMNS would contain all the column information (column type, it's integrity constraints etc).

A simple select Statement in SPUFI or QMF would help in retrieving this information.

A Sample SQL:

SELECT <DD-COLUMN1>,<DD-COLUMN2> FROM
SYSIBM.SYSCOLUMNS WHERE NAME = <COLUMN-NAME>

Note: Issuing a 'SELECT * FROM SYSIBM.SYSCOLUMNS' would display
lot of rows through which one can first check no. of columns the data
dictionary table has and accordingly issue the desired query.

Raphael Bacay · Posted: Fri Aug 03, 2007 9:40 am

The suggestion above will work. I would also like to suggest
if your system use DCLGEN libraries you can use ISPF 3.14 to
search for the particular column name in the PDS containing
the DCLGEN libraries. If you don't maintain DCLGEN libraries
then you can try the approach above.

Regards.