Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

selecting all tables with common column name

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
gaansin

New User


Joined: 25 Apr 2007
Posts: 1
Location: chennai

PostPosted: Wed Jun 27, 2007 4:00 pm    Post subject: selecting all tables with common column name
Reply with quote

i want to select all the tables in databse which is haveing some common column name. say example i want to select all the tables in the data base which is having column name empnumber. please help on this
Back to top
View user's profile Send private message

Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Wed Jun 27, 2007 6:40 pm    Post subject: Re: selecting all tables with common column name
Reply with quote

gaansin wrote:
i want to select all the tables in databse which is haveing some common column name. say example i want to select all the tables in the data base which is having column name empnumber. please help on this


This should give you a starting point.
Code:
  SELECT                                               
   C.TBNAME, C.COLNO,                                   
   C.NAME,                                             
   CASE C.COLTYPE                                       
    WHEN 'DATE'     THEN 'DATE'                         
    WHEN 'TIME'     THEN 'TIME'                         
    WHEN 'TIMESTMP' THEN 'TIMESTAMP'                   
    WHEN 'SMALLINT' THEN 'SMALLINT'                     
    WHEN 'INTEGER'  THEN 'INTEGER'                     
    WHEN 'CHAR'     THEN                               
     'CHAR(' CONCAT STRIP(CHAR(C.LENGTH)) CONCAT ')'   
    WHEN 'DECIMAL'  THEN                               
     'DEC(' CONCAT STRIP(CHAR(C.LENGTH)) CONCAT ','     
      CONCAT STRIP(CHAR(C.SCALE)) CONCAT ')'           
    WHEN 'VARCHAR'  THEN                               
     'VARCHAR(' CONCAT STRIP(CHAR(C.LENGTH)) CONCAT ')'
    ELSE '???'                                         
    END AS FORMAT,                                     
    CASE C.NULLS                                       
     WHEN 'N' THEN ' '                                 
     WHEN 'Y' THEN 'YES'                     
    END AS NULLS                             
FROM SYSIBM.SYSCOLUMNS C, SYSIBM.SYSTABLES T 
  WHERE C.TBNAME = T.NAME                     
    AND T.DBNAME = 'XXXXXXXX'                 
ORDER BY 1, 2                                 
FOR FETCH ONLY;                     
Back to top
View user's profile Send private message
Phrzby Phil

Active Member


Joined: 31 Oct 2006
Posts: 962
Location: Richmond, Virginia

PostPosted: Wed Jun 27, 2007 6:55 pm    Post subject:
Reply with quote

"Select" is a SQL term. I presume you mean determine or find out which tables in a given database have a given column name.

Query the system table sysibm.syscolumns C joined with sysibm.systables T on C.TBNAME = T.NAME where C.NAME = 'EMPNUMBER' and T.DBNAME = <database name>. The column with your desired table names is C.TBNAME.
Back to top
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Thu Jun 28, 2007 9:42 am    Post subject:
Reply with quote

Just query the catalog SYSIBM.SYSCOLUMNS.

Select * FROM SYSIBM.SYSCOLUMNS WHERE NAME = 'colname'.

It will give the list of all the tables/views containing a column by this name.
Back to top
View user's profile Send private message
bhushan.shete

New User


Joined: 14 Nov 2006
Posts: 17

PostPosted: Thu Jun 28, 2007 5:36 pm    Post subject: not an answer
Reply with quote

hi...

really gud ques..

i hav some access prob....

sooner i wil come wit exact answer

Bhush
Back to top
View user's profile Send private message
bhushan.shete

New User


Joined: 14 Nov 2006
Posts: 17

PostPosted: Thu Jun 28, 2007 5:37 pm    Post subject: not an answer
Reply with quote

hi...

really gud ques..

but none of the posts reflect correct ans.

i hav some access prob.... sooner i wil come wit exact answer

Bhush
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts SPUFI -- Joining 3 tables – data in... Sysaron DB2 2 Wed Mar 08, 2017 4:18 am
No new posts To know activities on db2 tables ashek15 DB2 4 Fri Mar 03, 2017 11:23 pm
No new posts ERM-Diagram of SysIBM-Catalog-Tables Auryn DB2 2 Fri Feb 17, 2017 6:22 pm
This topic is locked: you cannot edit posts or make replies. DB2 query Using Recursion, Converting... smilewithashu2 DB2 3 Tue Jan 03, 2017 12:50 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us