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
 

 

SQL Query To Get The Column Names Of A Table.

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

New User


Joined: 23 May 2007
Posts: 18
Location: Bangalore

PostPosted: Wed May 28, 2008 1:02 pm    Post subject: SQL Query To Get The Column Names Of A Table.
Reply with quote

Hi All,

Can anyone tell me whether there is sql query to get column names of a particular table or not?

Thanks in advance.
Back to top
View user's profile Send private message

the_gautam

Active User


Joined: 05 Jun 2005
Posts: 165
Location: Bangalore

PostPosted: Wed May 28, 2008 1:13 pm    Post subject:
Reply with quote

Code:
SELECT A.NAME, A.CREATOR, A.TYPE, A.TBCREATOR, A.TBNAME, B.NAME, BO.COLTYPE, B.LENGTH, B.SCALE FROM SYSIBM.SYSTABLES A, SYSIBM.SYSCOLUMNS B WHERE A.CREATOR=<user id> AND A.NAME=B.TBNAME AND A.CREATOR=B.TBCREATOR;


This query gives the details of the columns under a particular user-id. Play with it to meet your requirement.
Back to top
View user's profile Send private message
dmithunibm

New User


Joined: 23 May 2007
Posts: 18
Location: Bangalore

PostPosted: Wed May 28, 2008 1:43 pm    Post subject: Reply to: SQL Query To Get The Column Names Of A Table.
Reply with quote

Hi Gautam,

Thanks a lot.
Back to top
View user's profile Send private message
ksk

Active User


Joined: 08 Jun 2006
Posts: 356
Location: New York

PostPosted: Wed May 28, 2008 2:15 pm    Post subject:
Reply with quote

Hi,

To get the coloumn names of a particular table, execute select query and extract only one row. you will get all the colomn names.

SELECT * from Table-Name
fetch first row only;
Back to top
View user's profile Send private message
dmithunibm

New User


Joined: 23 May 2007
Posts: 18
Location: Bangalore

PostPosted: Wed May 28, 2008 2:22 pm    Post subject: Reply to: SQL Query To Get The Column Names Of A Table.
Reply with quote

Hi ksk,

I need just the name of the columns.In this case one record would also be fetched from the table along with the column list.
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Wed May 28, 2008 2:28 pm    Post subject:
Reply with quote

Hi,

If you need only the column-names, use any false condition such as
Code:
SELECT * FROM TABLE-NAME
WHERE 1=2
WITH UR;

it'll return only the column names; in QMF you can use function key "PF6" to "draw" the table. (If this is not the key, check the functioning of keys at the bottom of the screen in QMF, they are usually listed there.)
Back to top
View user's profile Send private message
ksk

Active User


Joined: 08 Jun 2006
Posts: 356
Location: New York

PostPosted: Wed May 28, 2008 2:37 pm    Post subject:
Reply with quote

Yes, if you give a condition which never satisfies, you will get only colomn names.

Gautam,

Can we retreive required colomn names for a particular table with your query?
Back to top
View user's profile Send private message
dmithunibm

New User


Joined: 23 May 2007
Posts: 18
Location: Bangalore

PostPosted: Wed May 28, 2008 2:42 pm    Post subject: Reply to: SQL Query To Get The Column Names Of A Table.
Reply with quote

Yes,that's what Anuj said.
Back to top
View user's profile Send private message
the_gautam

Active User


Joined: 05 Jun 2005
Posts: 165
Location: Bangalore

PostPosted: Wed May 28, 2008 4:23 pm    Post subject:
Reply with quote

Yes why not, see the output of the query and restrict the rows returned by putting the table-names or user-id in the WHERE clause. You may, choose the fields to be returned as per your requirement.
Back to top
View user's profile Send private message
am_ne

New User


Joined: 24 Mar 2007
Posts: 25
Location: Bangalore

PostPosted: Wed May 28, 2008 5:25 pm    Post subject: Reply to: SQL Query To Get The Column Names Of A Table.
Reply with quote

If you know the table name and the creator name then you can query SYSIBM.SYSCOLUMNS catalog table by using predicate TBNAME = 'table name' and TBCREATOR = 'creator'.
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 unload data from table with lob columns farhad_evan DB2 0 Sat Apr 22, 2017 1:32 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm


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