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
307 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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
 

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

Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

773 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