Home » SQL & PL/SQL » SQL & PL/SQL » count columns in table in sql
count columns in table in sql [message #351891] Fri, 03 October 2008 11:22 Go to next message
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



Re: count columns in table in sql [message #351901 is a reply to message #351891] Fri, 03 October 2008 12:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: count columns in table in sql [message #351991 is a reply to message #351901] Sat, 04 October 2008 12:10 Go to previous messageGo to next message
shrad2001
Messages: 20
Registered: September 2008
Location: faridabad
Junior Member
hI
I WANT TO KNOW COMMANDS TO COUNT NUMBER OF COLUMNS IN A TABLE

Re: count columns in table in sql [message #351992 is a reply to message #351991] Sat, 04 October 2008 12:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
Littlefoot
Messages: 20888
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?
Re: count columns in table in sql [message #351999 is a reply to message #351995] Sat, 04 October 2008 15:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: count columns in table in sql [message #352035 is a reply to message #351995] Sun, 05 October 2008 08:34 Go to previous messageGo to next message
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




Re: count columns in table in sql [message #352038 is a reply to message #352035] Sun, 05 October 2008 08:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
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


Re: count columns in table in sql [message #352065 is a reply to message #351891] Sun, 05 October 2008 14:55 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
dazzlingsidh
Messages: 1
Registered: October 2008
Junior Member
select COUNT(*)
from user_tab_columns
where table_name='X';
Re: count columns in table in sql [message #352217 is a reply to message #352215] Mon, 06 October 2008 11:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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
Re: count columns in table in sql [message #352379 is a reply to message #352375] Tue, 07 October 2008 07:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: count columns in table in sql [message #352613 is a reply to message #351891] Wed, 08 October 2008 10:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't use the same topic for another question, create a new one.

This topic is locked.

Regards
Michel
Re: count columns in table in sql [message #352694 is a reply to message #352613] Thu, 09 October 2008 00:55 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I split off the off-topic question (as we're supposed to do)

Unlocked this topic.
Re: count columns in table in sql [message #352706 is a reply to message #352694] Thu, 09 October 2008 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: count columns in table in sql [message #352709 is a reply to message #352706] Thu, 09 October 2008 01:38 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Then leave the thread alone and move on to the next one.
Previous Topic: Round values in a table
Next Topic: Simple proc throwing error ORA-00604, ORA-01401
Goto Forum:
  


Current Time: Fri Dec 02 22:39:31 CST 2016

Total time taken to generate the page: 0.10055 seconds