• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

In c# (or ADO.NET) is there a way to get all the column names of a given database table

I have an application written in c# that inserts rows into a database. Everyday when the application is started it connects to the DB and then gets a list of all columns in the database. I do this to make sure all the necessary columns are in the DB.

Currently I create an OdbcCommand object and issue the following query SELECT * FROM table WHERE 1 = 2. Then I iterate through the OdbcDataReader that is returned to get a list of all column names. The problem is that one of the DB I connect to has more then 18 million rows and the DB query optimizer is throwing out my query because it says it will time out on an 18 million row table span.

 Is there a better way to get the column names? The application connects to DB2, Oracle and SQL server databases

I am using Visual Studios 2005.
0
SSAFECS
Asked:
SSAFECS
  • 3
  • 3
  • 3
  • +2
5 Solutions
 
vbwizardryCommented:
Why don't you simply do [SELECT TOP 1 * FROM table] and ignore the values if you do not need them.
0
 
kkohler21Commented:
for ms sql
you could query the INFORMATION_SCHEMA table

SELECT     Column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE     (table_name = @table)

and for oracle
SELECT column_name
FROM USER_TAB_COLUMNS
WHERE table_name = 'MYTABLE'
0
 
Carl TawnSystems and Integration DeveloperCommented:
I think if you want to do this and have it work cross-database you will have to create a procedure to retrieve the data for each specific database type and retrieve it that way.

For example, you could have a procedure called "spGetColumnInfo" the implementation of which would be specific to  each database type.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
adatheladCommented:
Speaking purely from SQL Server point of view, there's a few ways you can get the schema including:

1) query INFORMATION_SCHEMA...
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='YourTable'

2) run the following query:
SET FMTONLY ON;
SELECT * FROM YourTable
SET FMTONLY OFF;

Setting the FMTONLY option like this means it only returns the schema for the resultset returned by the query, doesn't actually execute the query itself.

Now, if Oracle and DB2 have equivalents, then you could use this approach (just generate the appropriate SQL based on the RDBMS used).

Or, you could try "SELECT TOP 0 * FROM Table"
0
 
vbwizardryCommented:
Also depending on the version of ADO.NET you may be able to use GetSchema of DbConnection.
See here. http://www.davidhayden.com/blog/dave/archive/2006/01/15/2734.aspx
0
 
SSAFECSAuthor Commented:
vbwizardry:
I tried Why don't you simply do SELECT TOP 1 * FROM table but I am getting a SQL0104N error it says "TOP" is not a valid key word is there another way to do this?
0
 
kkohler21Commented:
Select *
FROM table
Fetch First 1 Row Only

0
 
SSAFECSAuthor Commented:
kkohler21: Because that is a SELECT * will that create a table span?
0
 
vbwizardryCommented:
what database are you using also did you try GetSchema?
0
 
kkohler21Commented:
im assuming that will return all the columns.  I dont use db2 but thats what i found on the internet to get a the first row of the table.
0
 
SSAFECSAuthor Commented:
I will try to use the GetSchema and see if that works on all my databases
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now