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 Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm


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