|
View previous topic :: View next topic
|
| Author |
Message |
gaansin
New User
Joined: 25 Apr 2007
Posts: 1
Location: chennai
|
|
|
|
| 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 |
|
 |
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| 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 |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006
Posts: 1042
Location: Richmond, Virginia
|
|
|
|
"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 |
|
|
vini_srcna
Active User
Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark
|
|
|
|
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 |
|
|
bhushan.shete
New User
Joined: 14 Nov 2006
Posts: 17
|
|
|
|
hi...
really gud ques..
i hav some access prob....
sooner i wil come wit exact answer
Bhush |
|
| Back to top |
|
|
bhushan.shete
New User
Joined: 14 Nov 2006
Posts: 17
|
|
|
|
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 |
|
|
|
|
