Home » SQL & PL/SQL » SQL & PL/SQL » Column count (merged)
Column count (merged) [message #212529] Fri, 05 January 2007 12:25 Go to next message
durai
Messages: 38
Registered: December 2006
Member
Hello, I had a doubt regarding a selection of table

SQL> select * from emp;

select * from emp
*
ERROR at line 1:
ORA-00942: table or view does not exist

but when i gave the command
select * from tab;
the table exist there, how to remove

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
emp TABLE


also friends tell me,
how to calculate Total Number Of columns in table. Example: if A table have 8 columns means , the output should 8

[Updated on: Fri, 05 January 2007 12:29]

Report message to a moderator

Re: help!!! [message #212530 is a reply to message #212529] Fri, 05 January 2007 12:31 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
See what happens when you create a table using double quotes to go against the standard and force a table in lowercase?

You'll have to use
select * from "emp";

now in every SELECT statement and in every stored procedure, function, sql script, etc. It's ripe for error.

Look in USER_TAB_COLUMNS for the answer to the second question.

[Updated on: Fri, 05 January 2007 12:32]

Report message to a moderator

Re: help!!! [message #212533 is a reply to message #212530] Fri, 05 January 2007 13:19 Go to previous messageGo to next message
durai
Messages: 38
Registered: December 2006
Member
i tried USER_TAB_COLUMNS
but i couldn't found how to get the count of column of a particular table
Re: help!!! [message #212535 is a reply to message #212533] Fri, 05 January 2007 13:28 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Just the same as you'd find ANY count - using the COUNT function.
SELECT table_name, COUNT(*) number_of_columns
FROM user_tab_columns
GROUP BY table_name;
column count [message #212813 is a reply to message #212529] Mon, 08 January 2007 07:59 Go to previous messageGo to next message
durai
Messages: 38
Registered: December 2006
Member
first i like to thank littlefoot,
i tried this query
SELECT table_name, COUNT(*) number_of_columns
FROM user_tab_columns
GROUP BY table_name;

using this i got all the tables column count, but i need only a particular table column count

plz help me to solve this
Re: column count [message #212817 is a reply to message #212813] Mon, 08 January 2007 08:03 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Try using the WHERE clause
Re: column count [message #212818 is a reply to message #212813] Mon, 08 January 2007 08:04 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Just modify your query to:

SELECT table_name, COUNT(*) number_of_columns
FROM user_tab_columns
WHERE table_name = '<YOUR_TABLE_NAME_IN_CAPS>'
GROUP BY table_name;
Re: column count [message #212862 is a reply to message #212818] Mon, 08 January 2007 10:42 Go to previous messageGo to next message
durai
Messages: 38
Registered: December 2006
Member
thanx ebrian, thanx a lot it's working
i like to thanx others also
Re: column count [message #212899 is a reply to message #212862] Mon, 08 January 2007 15:15 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
@durai: please, do not open a new topic when discussing the same problem.
Re: column count [message #213078 is a reply to message #212899] Tue, 09 January 2007 06:55 Go to previous messageGo to next message
durai
Messages: 38
Registered: December 2006
Member
ok sorry!, here after it will not happen
Re: Column count (merged) [message #213101 is a reply to message #212529] Tue, 09 January 2007 08:40 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
Hi Durai,

Execute this query :

Quote:
Trivedi>create table "emp"
2 (id number(2));


and then give execute this query :

Quote:
Trivedi> select table_name,count(*) "No of Columns"
2 from user_tab_columns
3 where table_name in ('emp','EMP')
4* group by table_name
Trivedi>/

TABLE_NAME No of Columns
------------------------------ -------------
EMP 8
emp 1


If your table name is in lower case then you have to do like that .

I think joy wants to explain this to you but .

Bye
Ashu

[Updated on: Tue, 09 January 2007 08:41]

Report message to a moderator

Re: Column count (merged) [message #213159 is a reply to message #213101] Tue, 09 January 2007 12:36 Go to previous message
durai
Messages: 38
Registered: December 2006
Member
thanx Ashu
i got it
Previous Topic: order by variable
Next Topic: foreign key
Goto Forum:
  


Current Time: Wed Dec 07 10:58:35 CST 2016

Total time taken to generate the page: 0.08514 seconds