OraFAQ Forum: SQL & PL/SQL » Table name which has the maximum columns

Home » SQL & PL/SQL » SQL & PL/SQL » Table name which has the maximum columns

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Table name which has the maximum columns [message #11235]

Mon, 15 March 2004 02:19

Hem Anand
Messages: 68
Registered: March 2004

Member

I would like to know the table_name which has the maximum number of columns.

I do have the solution still need to be more optimized

Report message to a moderator

Re: Table name which has the maximum columns [message #11239 is a reply to message #11235]

Mon, 15 March 2004 06:19

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

How does this query perform? (Note, uses analytic functions and therefore requires at least version 8.1.6.)

SELECT schema_name
,      table_name
,      max_cols
FROM  (SELECT   u.name                   schema_name
       ,        o.name                   table_name
       ,        COUNT(*)                 cols
       ,        MAX(COUNT(*)) OVER ()    max_cols
       FROM     sys.col$     c
       ,        sys.obj$     o
       ,        sys.user$    u
       WHERE    u.name                 = UPPER('<font color=red><i>your_schema</i></font>')
       AND      o.obj#                 = c.obj#
       AND      o.owner#               = u.user#
       AND      o.type#                = 2 -- tables only
       AND      BITAND(c.property, 32) = 0 -- non-hidden column
       GROUP BY u.name
       ,        o.name)
WHERE  cols = max_cols

Hope this helps,

Art.

Report message to a moderator

Re: Table name which has the maximum columns [message #11251 is a reply to message #11239]

Mon, 15 March 2004 23:08

Art
Messages: 6
Registered: July 2002

Junior Member

I want the table name in the present schema and all user owned tables

Report message to a moderator

Re: Table name which has the maximum columns [message #11261 is a reply to message #11251]

Tue, 16 March 2004 11:54

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

quote from Hem anand:

----------------------------------------------------------------------

I want the table name in the present schema and all user owned tables

----------------------------------------------------------------------
OK, so try this related query on for size:

SELECT schema_name
,      table_name
,      max_cols
FROM  (SELECT   u.name                   schema_name
       ,        o.name                   table_name
       ,        COUNT(*)                 cols
       ,        MAX(COUNT(*)) OVER ()    max_cols
       FROM     sys.col$     c
       ,        sys.obj$     o
       ,        sys.user$    u
       WHERE    u.name                 = UPPER(<font color=red>USER</font>)
       AND      o.obj#                 = c.obj#
       AND      o.owner#               = u.user#
       AND      o.type#                = 2 -- tables only
       AND      BITAND(c.property, 32) = 0 -- non-hidden column
       GROUP BY u.name
       ,        o.name)
WHERE  cols = max_cols
/

USER returns the name of the session user (the user who logged on) with the datatype VARCHAR2.

HTH,

Art.

Report message to a moderator

Re: Table name which has the maximum columns [message #11363 is a reply to message #11261]

Mon, 22 March 2004 23:00

Hem Anand
Messages: 68
Registered: March 2004

Member

--Something Similar to that

SELECT table_name
FROM user_tab_columns
GROUP BY table_name
HAVING COUNT(column_name) =
( SELECT MAX(COUNT(column_name))
FROM user_tab_columns
GROUP BY table_name)

Report message to a moderator

Previous Topic:	strings & character validation
Next Topic:	How to restart sequence

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 00:08:44 CDT 2024