4 Replies Latest reply on May 16, 2012 8:50 AM by Keith Jamieson

    camparing no of columns in differe schemas

    926231
      i have two schemas, want to find no of columns and table in both scehmas.
        • 1. Re: camparing no of columns in differe schemas
          Mahir M. Quluzade
          Hi Ramesh

          You can use DBA_TAB_COLUMNS table.


          Mahir M. Quluzade
          1 person found this helpful
          • 2. Re: camparing no of columns in differe schemas
            Keith Jamieson
            I assume you want to know the number of columns per table and not just the number of columns.

            You can use the datadictionary view dba_tab_cols
            select table_name, count(column_name)
            from dba_tab_cols
            where owner = 'SCHEMA_NAME'
            group by table_name
            The number of rows returned will give you the number of tables in the schema , and against each table you will have a count of the number of columns.

            You may have to give your user explicit privileges to select from dba_tab_cols
            1 person found this helpful
            • 3. Re: camparing no of columns in differe schemas
              V prasad
              try this


              CREATE TABLE USER_TABLE_COLUMS(TABLE_NAME varchar2(50), COLUMN_NAME varchar2(50), DATA_TYPE varchar2(50));
              insert into USER_TABLE_COLUMS (select TABLE_NAME, COLUMN_NAME, DATA_TYPE from all_tab_columns where ower = ur_schema)

              SELECT b.TABLE_NAME,
              b.COLUMN_NAME,
              b.DATA_TYPE
              FROM schema.USER_TAB_COLS b, all_user_columns C
              where b.TABLE_NAME = c.TABLE_NAME
              and b.COLUMN_NAME = c.COLUMN_NAME
              and b.DATA_TYPE <> c.DATA_TYPE;
              1 person found this helpful
              • 4. Re: camparing no of columns in differe schemas
                Keith Jamieson
                The reason I used dba_tab_cols is that it will get all the tables in the database for the schema specified.

                If you use user_tab_cols you only get tables in the schema select from. If you use all_tab_cols you only get tables in your schema and tables that you have privileges to select from, therefore its possible to get an incorrect count using all_tab_cols view.