count number of coulmns in table?

[Kaustubh G Sharma]

how to get count of number of coulmns in a table in sql query?

[Lester Burnham]

java.sql.Connection.getMetaData() is the DB-independent way to do this.

[Pravin Shirke]

hi kaustubh,
create a ResultSetMetaData object on resultset and get the column count of your sql query. like this

          ResultSetMetaData rsmd = rs.getMetaData();
          rsmd.getColumnCount();

where rs is the resultset object

Hope this helps

[Kaustubh G Sharma]

sir I want this is sql query not on java side...

[Lester Burnham]

Then you need to consult the documentation of whatever DB you're using. There's no standard for that.

[Sandeep Sanaboyina]

Which database are you using..???

[Kaustubh G Sharma]

Microsoft SQL Server 2005

[Christophe Verré]

Kaustubh G Sharma wrote:sql 2005

You mean Microsoft SQL Server 2005 ?

[Kaustubh G Sharma]

yes sir

[Sandeep Sanaboyina]

Kaustubh G Sharma wrote:Microsoft SQL Server 2005

No experience in SQL Server

Googled it though.. found the below link. Check post 11 in it. Hope it helps.

http://blog.sqlauthority.com/2007/01/10/sql-server-query-to-find-number-rows-columns-bytesize-for-each-table-in-the-current-database-find-biggest-table-in-database/

[Rob Spoor]

If I search for ms sql server column count the sixth hit is http://forums.devx.com/archive/index.php/t-25101.html. This shows you how to do it.

[Kaustubh G Sharma]

thanks rob well I found this idea before not found any single way out to solve the problem....or atleast possible please let me knew how to do it in "derby" will be very greatful...

[Paul Sturrock]

Sorry, are you looking for a way that will work across all databases? Lester has already pointed you at the solution for that.

[Kaustubh G Sharma]

Come to know that we don't have any standard for all the databases for the above query but want to know the solution for "derby"

[Lester Burnham]

Substituting "derby" for "ms sql server" in the search Rob mentioned would be the obvious first step to try; have you done that?

[Kaustubh G Sharma]

yup I already did that..

[Lester Burnham]

So the problem is solved now?

[Kaustubh G Sharma]

No Sir I want the same thing for derby....

[Paul Sturrock]

Does the documentation not tell you this? Given your responses so far I'm not sure anyone here knows. You are probably going to get your answer a bit faster if you check there.

[Rob Spoor]

Let me make it clear:
There is no query that will work on all database systems.
Sorry that I made it so big, but the message didn't seem to get through.

So you have two options:
- use a different query for all database systems you want to support (using Google initially to find those queries)
- follow Lester's advice: check out DatabaseMetaData; you can get an instance using Connection.getMetaData(). There is one method that returns a ResultSet for all columns. You then only need to go through the ResultSet, incrementing a counter for each result:

int columns = 0;
while (rs.next())
{
    columns++;
}

Make sure to close() the ResultSet when done.

[Christophe Verré]

Rob Prime wrote:There is no query that will work on all database systems.

There is no such query... I say that for people who could take it out of its context.

[Lester Burnham]

Christophe Verré wrote:I say that for people who could take it out of its context.

You must have ... interesting ... people at your workplace

[Kaustubh G Sharma]

There is no query that will work on all database systems.

sad to here that....


No Solution found for DERBY as well...

[Jaikiran Pai]

Kaustubh G Sharma wrote:

No Solution found for DERBY as well...

Did you check their manual? Specifically, did you check their documentation on system tables? Based on that documentation, I can see that it's possible to find the column count. You'll need admin privileges to query those system tables.

[Kaustubh G Sharma]

thanks jaikiran your link really help me out to solve the problem