Solved

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

Posted on 2009-03-30
13
303 Views
Last Modified: 2013-12-17
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
Comment
Question by:SSAFECS
  • 3
  • 3
  • 3
  • +2
13 Comments
 
LVL 7

Accepted Solution

by:
vbwizardry earned 200 total points
ID: 24019425
Why don't you simply do [SELECT TOP 1 * FROM table] and ignore the values if you do not need them.
0
 

Assisted Solution

by:kkohler21
kkohler21 earned 100 total points
ID: 24019436
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
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 100 total points
ID: 24019443
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
 
LVL 23

Assisted Solution

by:adathelad
adathelad earned 100 total points
ID: 24019456
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
 
LVL 7

Assisted Solution

by:vbwizardry
vbwizardry earned 200 total points
ID: 24019471
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:SSAFECS
ID: 24021131
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
 

Expert Comment

by:kkohler21
ID: 24021252
Select *
FROM table
Fetch First 1 Row Only

0
 

Author Comment

by:SSAFECS
ID: 24021307
kkohler21: Because that is a SELECT * will that create a table span?
0
 
LVL 7

Expert Comment

by:vbwizardry
ID: 24021392
what database are you using also did you try GetSchema?
0
 

Expert Comment

by:kkohler21
ID: 24021424
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
 

Author Comment

by:SSAFECS
ID: 24021508
I will try to use the GetSchema and see if that works on all my databases
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
coded character set iptc field within a jpeg 1 70
Code works but I need to redo it and assign values to Model 7 33
C# Single Form 8 28
Web Form VB.Net  import CSV 4 26
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now