OraFAQ Forum: SQL & PL/SQL » count columns in table in sql

Home » SQL & PL/SQL » SQL & PL/SQL » count columns in table in sql

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

count columns in table in sql [message #351891]

Fri, 03 October 2008 11:22

shrad2001
Messages: 20
Registered: September 2008
Location: faridabad

Junior Member

Hello

I want to know how can i count the number of columns in table

i view this commnand
select count(*) from information_schema.columns
where table_name='x';
but when i m using this i got error that table does not exist

Can any one help me to find this

Thanks
smily

Report message to a moderator

Re: count columns in table in sql [message #351901 is a reply to message #351891]

Fri, 03 October 2008 12:16

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

user_tab_columns

A useful view to search for dictionary view:

SQL> desc dict
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 TABLE_NAME                                VARCHAR2(30)
 COMMENTS                                  VARCHAR2(4000)

Regards
Michel

Report message to a moderator

Re: count columns in table in sql [message #351991 is a reply to message #351901]

Sat, 04 October 2008 12:10

shrad2001
Messages: 20
Registered: September 2008
Location: faridabad

Junior Member

hI
I WANT TO KNOW COMMANDS TO COUNT NUMBER OF COLUMNS IN A TABLE

Report message to a moderator

Re: count columns in table in sql [message #351992 is a reply to message #351991]

Sat, 04 October 2008 12:46

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

1/ Don't yell (post in UPPER case)
2/ Read forum guide
3/ This has been answered, just think, try and work a little bit

Regards
Michel

[Updated on: Sat, 04 October 2008 12:47]

Report message to a moderator

Re: count columns in table in sql [message #351995 is a reply to message #351891]

Sat, 04 October 2008 14:51

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

shrad2001 wrote on Fri, 03 October 2008 18:22

select count(*)
from information_schema.columns
where table_name='x';

Just wondering - what would the bold part be? Where did you find that information? Are you talking Oracle here?

Report message to a moderator

Re: count columns in table in sql [message #351999 is a reply to message #351995]

Sat, 04 October 2008 15:45

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

This is MySQL, I thought OP wanted to get the same thing in Oracle but reading back his post, I think it was just a mispost.

Regards
Michel

Report message to a moderator

Re: count columns in table in sql [message #352035 is a reply to message #351995]

Sun, 05 October 2008 08:34

shrad2001
Messages: 20
Registered: September 2008
Location: faridabad

Junior Member

Hi

select count(*)
from information_schema.columns
where table_name='x';

I have seen above commnads. but when i m using this in sql*plus it means oracle 8i database
i got message that table information_schema doesnot exist

pls tell me command to count number of columns in table

Report message to a moderator

Re: count columns in table in sql [message #352038 is a reply to message #352035]

Sun, 05 October 2008 08:58

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Michel Cadot wrote on Fri, 03 October 2008 19:16

user_tab_columns

A useful view to search for dictionary view:

SQL> desc dict
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 TABLE_NAME                                VARCHAR2(30)
 COMMENTS                                  VARCHAR2(4000)

Regards
Michel

Report message to a moderator

Re: count columns in table in sql [message #352065 is a reply to message #351891]

Sun, 05 October 2008 14:55

vijaya_krishna_birju
Messages: 1
Registered: October 2008

Junior Member

Hi,
he table which you are refering is not part of oracle. If you want to find no.of coulumns then try running the below query.

SELECT TABLE_NAME,MAX(COULMN_ID) FROM USER_TAB_COLUMNS
GROUP BY TABLE_NAME

Thanks -- Vj

[Updated on: Mon, 06 October 2008 00:45] by Moderator

Report message to a moderator

Re: count columns in table in sql [message #352083 is a reply to message #352065]

Mon, 06 October 2008 00:47

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

@vijaya_krishna_birju

Please read OraFAQ Forum Guide and:
1/ Don't put solution only clue
2/ Format your post
3/ Don't post link to your blog unless it can directly help for the current question

Regards
Michel

[Updated on: Mon, 06 October 2008 00:47]

Report message to a moderator

Re: count columns in table in sql [message #352215 is a reply to message #351891]

Mon, 06 October 2008 11:38

dazzlingsidh
Messages: 1
Registered: October 2008

Junior Member

select COUNT(*)
from user_tab_columns
where table_name='X';

Report message to a moderator

Re: count columns in table in sql [message #352217 is a reply to message #352215]

Mon, 06 October 2008 11:56

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

@dazzlingsidh,

Welcome to the forum.
You should read my previous post and follow it:

Michel Cadot wrote on Mon, 06 October 2008 07:47

Please read OraFAQ Forum Guide and:
1/ Don't put solution only clue
2/ Format your post

And I'd add: read the previous answers and don't repeat them.

Regards
Michel

[Updated on: Mon, 06 October 2008 11:57]

Report message to a moderator

Re: count columns in table in sql [message #352375 is a reply to message #351891]

Tue, 07 October 2008 06:45

minaik
Messages: 1
Registered: March 2007
Location: Bangalore

Junior Member

Hi,

Use below SQL to get the no. of columns...

SELECT table_name, COUNT (column_name)
FROM user_tab_columns
WHERE UPPER (table_name) LIKE 'CUSTOMER_BUDGETING'
GROUP BY table_name

Regards,
Manish

Report message to a moderator

Re: count columns in table in sql [message #352379 is a reply to message #352375]

Tue, 07 October 2008 07:00

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

@minaik

Michel Cadot wrote on Mon, 06 October 2008 18:56

Welcome to the forum.
You should read my previous post and follow it:

Michel Cadot wrote on Mon, 06 October 2008 07:47

Please read OraFAQ Forum Guide and:
1/ Don't put solution only clue
2/ Format your post

And I'd add: read the previous answers and don't repeat them.

Regards
Michel

Report message to a moderator

Re: count columns in table in sql [message #352613 is a reply to message #351891]

Wed, 08 October 2008 10:40

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Don't use the same topic for another question, create a new one.

This topic is locked.

Regards
Michel

Report message to a moderator

Re: count columns in table in sql [message #352694 is a reply to message #352613]

Thu, 09 October 2008 00:55

Frank
Messages: 7901
Registered: March 2000

Senior Member

I split off the off-topic question (as we're supposed to do)

Unlocked this topic.

Report message to a moderator

Re: count columns in table in sql [message #352706 is a reply to message #352694]

Thu, 09 October 2008 01:29

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Frank wrote on Thu, 09 October 2008 07:55

... (as we're supposed to do) ...

Sometimes I'm tired to manage those who refuse to follow the guidelines.

Regards
Michel

Report message to a moderator

Re: count columns in table in sql [message #352709 is a reply to message #352706]

Thu, 09 October 2008 01:38

Frank
Messages: 7901
Registered: March 2000

Senior Member

Then leave the thread alone and move on to the next one.

Report message to a moderator

Previous Topic:	Round values in a table
Next Topic:	Simple proc throwing error ORA-00604, ORA-01401

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Oct 05 08:09:11 CDT 2025