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
300 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

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 …
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
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 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…

708 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

13 Experts available now in Live!

Get 1:1 Help Now