locked
How to see Columns and corresponding Datatypes in a Table RRS feed

  • Question

  • User4600663 posted

     

    Hi Friends,

                     I am using PL/SQL Developer. I have a Table "C_PARTY_HXRF". Now I want see the Column Names and the corresponding Data types of this Table. Which Command should I Use?

    Thanks in Advance.

      

    Thursday, January 13, 2011 2:37 AM

All replies

  • User1949625403 posted

    hi thenraj write query like this to check columnnames and datatype

    SELECT column_name 'Column Name',
    data_type 'Data Type',
    CHARacter_maximum_length 'Maximum Length'
    FROM information_schema.columns
    WHERE table_name = 'Address'



    Thursday, January 13, 2011 3:31 AM
  • User-1181492241 posted

    Try this query to get the column name and datatype of table C_PARTY_HXRF.

    Select COLUMN_NAME, DATA_TYPE from user_tab_columns where table_name='C_PARTY_HXRF' 

    Thursday, January 13, 2011 3:44 AM
  • User843229376 posted

    Write your TableName in database ,place cursor on it and press ALT+F1

    You will get following fields related to Table

    Column_name, Type ,Computed, Length, Prec,Scale,Nullable,TrimTrailingBlanks, FixedLenNullInSource, Collation

    Thursday, January 13, 2011 3:52 AM
  • User1949625403 posted

    hi thenraj check this link here i explain how to get column names and corresponding data types of particular table with demo

    http://aspdotnet-suresh.blogspot.com/2010/12/how-to-insert-data-into-xml-and-how-to.html

    Thursday, January 13, 2011 4:08 AM
  • User1867929564 posted

    if you are just to view design then

    sp_help C_PARTY_HXRF

    Thursday, January 13, 2011 4:28 AM
  • User4600663 posted

    Thanks a lot Suresh,Rajesh,Jassu,KumarHarsh for your Help.

    @KumarHarsh

    I have already tried the below command But it showed me an Error. I think the below command will work in SQL server but not in PL/SQL Developer.

    sp_help C_PARTY_HXRF

    @Jassu107

    I Just logged into the Database and typed the TableName C_PARTY_HXRF and pressed Alt+F1.But I could not find any Output. Please correct me If I did it wrongly.

    @Rajesh

    The below Query given by you worked Perfectly.

    Select COLUMN_NAME, DATA_TYPE from user_tab_columns where table_name='C_PARTY_HXRF'.

    I have a question here, suppose if I want to see the Specified Columns Datatypes Is it possible?

    C_PARTY_HXRF has more than 40 Columns,Suppose If I want to see the datatypes of this 3 columns PRVDD_FULL_NM,PRVDD_MIDDLE_NM,CM_ONLY_IND.What would be the command?.

    @Suresh

    I tried the below command but it showed me an Error. Please let me know if there is any modification.

    Select COLUMN_NAME,DATA_TYPE from Information_schema.columns where table_name='C_PARTY_HXRF'

    I browsed the below URL

    http://aspdotnet-suresh.blogspot.com/2010/12/how-to-insert-data-into-xml-and-how-to.html

    But it said to Insert data into XML. Could not be able to find Query for finding columns Datatypes.

    @all

    And I have one more question What will be the command for viewing columnnames,Datatypes in Views and Procedures in SQL Developer

    Thanks All

     

     

     

     

    Thursday, January 13, 2011 7:32 AM
  • User843229376 posted

    I Just logged into the Database and typed the TableName C_PARTY_HXRF and pressed Alt+F1.But I could not find any Output. Please correct me If I did it wrongly.


    Well it must have been done. You should have cursor on the TABLENAME and then click ALT+F1.




    Thursday, January 13, 2011 7:52 AM
  • User-2001332922 posted

    I guess you have posted in wrong forum. Anyways, the problem seems to be of Oracle. if so, try

    desc <table name> this is actually the SQL plus (similar to Query analyzer of SQL Server) command this will give you the desired output.

    confirm about your tries.

     

     

     

     

     

     

     

    I guess

    Thursday, January 13, 2011 7:54 AM
  • User1949625403 posted

    hi thenraj i think that error because u didn't mention your database may be that is problem and sorry i gave wrong link to you now i corrected the code check this link for demo now it will work for you

    USE MySampleDB
    
    GO
    
    SELECT column_name 'Column Name',
    
    data_type 'Data Type',
    
    CHARacter_maximum_length 'Maximum Length'
    
    FROM information_schema.columns
    
    WHERE table_name = 'Country'


    http://aspdotnet-suresh.blogspot.com/2011/01/how-to-get-column-names-and.html


    Thursday, January 13, 2011 8:08 AM
  • User4600663 posted

    @Jasu107

    I tried as you said, but still it was unsuccessful.

    @aspnet2sams

    I tried the below command but it showed me an Error.I think this command will work only in Oracle but not in PL/SQL Developer

    desc C_PARTY_HXRF 

    Thursday, January 13, 2011 8:27 AM
  • User-2001332922 posted

    ok. dude I think you need the structure of table to know the column names, datatypes and stuff tha's it. just plug in to Oracle SQL Plus by scott/tiger and get the info work accordingly. I also think that you don't have to implement the structure at any point of your PL/SQL sp's, funcs etc code. Just to know the structure of table the solution will do.

    good luck.

     

    Thursday, January 13, 2011 8:59 AM
  • User4600663 posted

    @Suresh

    I checked the URL,It was correct.I tried the below code, but still it was unsuccessful.I want you to know that I opened the PL/SQL Developer and logged in by providing username,password and already specified Database name.


    USE P0_OBN  
     
    GO  
     
    SELECT column_name 'Column Name',  
     
    data_type 'Data Type',  
     
    CHARacter_maximum_length 'Maximum Length' 
     
    FROM information_schema.columns  
     
    WHERE table_name = 'C_PARTY_HXRF' 


    @aspnet2sams

    Exactly as you said I need the structure of the table.Unexpectately the Oracle SQL Plus is not working currently to try for it. I must get the details through PL/SQL Developer.

     

    Thursday, January 13, 2011 9:30 AM
  • User-1181492241 posted

    If you want to see the details of only 3 columns set the where condition as below

    Select COLUMN_NAME, DATA_TYPE from user_tab_columns where table_name='C_PARTY_HXRF'
    AND COLUMN_NAME IN ('PRVDD_FULL_NM','PRVDD_MIDDLE_NM','CM_ONLY_IND');


     

    Thursday, January 13, 2011 9:47 AM
  • User-2001332922 posted

    Sorry for wrong guessing on availability of SQL Plus. I can suggest few more alternatives these are:

    1. Browser version (same as SQL plus login....and continue to work!!)

    2. dos command prompt version (forgot how to make it work, sorry again)

    I am actually recalling and giving you suggestions as currently I don't have oracle installed to verify and confirm you evidently about my post. please pardon me in case u still strucks.

     

    Thursday, January 13, 2011 10:00 AM
  • User1949625403 posted

    hi thenraj r u getting this type error

    <mce:style><!-- table.MsoNormalTable { line-height: 115%; font-size: 11pt; font-family: "Calibri","sans-serif"; } --></mce:style><style mce_bogus="1"><!-- table.MsoNormalTable { line-height: 115%; font-size: 11pt; font-family: "Calibri","sans-serif"; } --></style>

    Invalid object name 'information_schema.columns'

    this error because of your case sensitive database to rectify that error check this post i modified the post to solve that problem

    http://aspdotnet-suresh.blogspot.com/2011/01/how-to-get-column-names-and.html

    Thursday, January 13, 2011 10:16 PM
  • User-2001332922 posted

    Are u still facing the problem? if not, please guide us all so that we can recollect (in case forgotten) get to know the solutions.

    thanks

    Sunday, January 16, 2011 12:34 PM
  • User4600663 posted

    @Rajesh

    Perfect.The below query worked well.Thanks.

    Select COLUMN_NAME, DATA_TYPE from user_tab_columns where table_name='C_PARTY_HXRF'
    AND COLUMN_NAME IN ('PRVDD_FULL_NM','PRVDD_MIDDLE_NM','CM_ONLY_IND');


    @suresh

    I checked and ran the below query.It showed this Error "ORA-00900 : invalid SQL Statement"

    USE P0_OBN  
     
    GO  
     
    SELECT column_name 'Column Name',  
     
    data_type 'Data Type',  
     
    CHARacter_maximum_length 'Maximum Length' 
     
    FROM INFORMATION_SCHEMA.COLUMNS  
     
    WHERE table_name = 'C_PARTY_HXRF' 

    Please correct me If anything is wrong


    @aspnet2sams

    Thanks a lot for providing alternatives. My problem is almost solved.But still I have some questions. I have asked below.Please check it.Thanks in Advance.

    @all

    What will be the command for viewing columnnames,Datatypes in Views and Procedures in SQL Developer

    Monday, January 17, 2011 6:15 AM
  • User-1181492241 posted

    Hi,

    Use this to get Stored procedure information

    SELECT package_name, object_name, argument_name, data_type FROM USER_ARGUMENTS; 

    To get View information use this,

    SELECT table_name, column_name
    FROM user_updatable_columns
    WHERE table_name IN (
      SELECT view_name
      FROM user_views);

    Monday, January 17, 2011 7:55 PM
  • User4600663 posted

    @Rajesh

    I have a view "ORG_MULTI_FACTCS_WITH_LOCTN" and I have Column Names "BOB_COT","BOB_CUST_ID".My front End Tool is Pointing to Coulumn names through that View only.Is it Mandatory that I should provide the Table Names also to know the details. Is it possible to get the required details by having view and Column names only.
     

    Wednesday, January 19, 2011 9:54 AM
  • User-1181492241 posted

    For getting the columns of view, ORG_MULTI_FACTCS_WITH_LOCTN use this query

    SELECT table_name, column_name
    FROM user_updatable_columns
    WHERE table_name IN (
      SELECT view_name
      FROM user_views where view_name='ORG_MULTI_FACTCS_WITH_LOCTN');

    ---------

    Another method is to use the commad DESCRIBE ORG_MULTI_FACTCS_WITH_LOCTN.
    From Plsql developer File menu select New->Command Window

    In the new window, after the line SQL>, type the command and press Enter

    as below

    SQL> DESCRIBE ORG_MULTI_FACTCS_WITH_LOCTN;

    You can use the DESCRIBE to view your table and stored procedure details also

    SQL> DESCRIBE <TABLE_NAME>;
    SQL> DESCRIBE <Package_name>.<sp name>;

    Hope it helps.

    Wednesday, January 19, 2011 7:42 PM
  • User4600663 posted

    @ Rajesh

    I ran the below Query

    SELECT table_name, column_name
    FROM user_updatable_columns
    WHERE table_name IN (
      SELECT view_name
      FROM user_views where view_name='ORG_MULTI_FACTCS_WITH_LOCTN');

     and got the below output

    TABLE_NAME                                                      COLUMN_NAME
    ORG_MULTI_FACTCS_WITH_LOCTN                    ADDR_LINE2_TXT
    ORG_MULTI_FACTCS_WITH_LOCTN                    POSTAL_CODE

     

    and then what will be the Query to know the Data Types of "ADDR_LINE2_TXT","POSTAL_CODE" Columns?

     

    Thanks in Advance

     

    Friday, January 21, 2011 3:23 AM
  • User-1181492241 posted

    I think using this query, it is not possible to get the datatype of columns in View.

    But you can use the DECRIBE View_name command as mentioned in my previous post.

    Friday, January 21, 2011 3:46 AM
  • User4600663 posted

    When I ran the below Query. It is throwing Error. What could be the Correct Query?

    DESCRIBE ORG_MULTI_FACTCS_WITH_LOCTN

    Friday, January 21, 2011 4:41 AM
  • User-1181492241 posted

    Are you using the command window in Plsql developer as I mentioned in the previous post?

    From Plsql developer File menu select New->Command Window

    In the new window, after the line SQL>, type the command and press Enter

    as below

    SQL> DESCRIBE ORG_MULTI_FACTCS_WITH_LOCTN;

    Or you can use the Object Browser to view the columns, which is the easiest way. 

    Friday, January 21, 2011 6:07 AM
  • User4600663 posted

    @ Rajesh

    Perfect. It worked well. By the way I have still some question below. As I have deployed into new project. I need to learn new things. Thanks a lot for all your help. It is highly appreciated.

    @all

    I have a below query

    select distinct
           DATA_SRC_CD  as  Source,
           FILE_NM  as  File_Name,
           from PKG_SFI_REPORTS.DATALOAD2SUMMARYETLRPRT("FACTS","CM_DDL_PARTY_2011_01_12_08_24_09.DAT")

    In the above Query PKG_SFI_REPORTS => Package, DATALOAD2SUMMARYETLRPRT=> Procedure.

    "FACTS" and "CM_DDL_PARTY_2011_01_12_08_24_09.DAT" => Parameters

    Now I need to know the Data Types of "DATA_SRC_CD" and "FILE_NM". How can I Know?

     

    Friday, January 21, 2011 8:20 AM
  • User-1181492241 posted

    Please check my previous post to get the SP details.

    Use this to get Stored procedure information

    SELECT package_name, object_name, argument_name, data_type FROM USER_ARGUMENTS; 

     

    So for getting information of package_name='PKG_SFI_REPORTS' use the below query,

    SELECT package_name, object_name, argument_name, data_type FROM USER_ARGUMENTS
    where package_name='PKG_SFI_REPORTS' and object_name='DATALOAD2SUMMARYETLRPRT';

     

    Sunday, January 23, 2011 9:54 PM
  • User4600663 posted

     @ Rajesh

    By running the below query I am able to Know the Data types of the arguments of Stored Procedure. But I need to Know the data types of "DATA_SRC_CD","FILE_NM " columns. Can you help me?

    SELECT package_name, object_name, argument_name, data_type FROM USER_ARGUMENTS
    where package_name='PKG_SFI_REPORTS' and object_name='DATALOAD2SUMMARYETLRPRT';

     

    Tuesday, January 25, 2011 8:43 AM
  • User4600663 posted

    Any update of my above question? Still it is not answered?

    Friday, January 28, 2011 11:00 AM