Click here to Skip to main content
12,398,225 members (52,662 online)
Click here to Skip to main content
Add your own
alternative version


2 bookmarked

List SQL Server table column name as rows

, 3 May 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
This is an alternative for "List SQL Server table column name as rows"

A more database-agnostic way to get information about the columns of a table in .NET is to use the GetSchemaTable method of an IDataReader. Simply perform ExecuteReader on an SQL statement like

SELECT * FROM source WHERE 0=1

(being sure to protect against SQL injection of course) and iterate the Rows in the resultant DataTable. The 0=1 ensures that we don't waste bandwidth transferring data needlessly. You can (and should) Close the IDataReader as soon as you have the DataTable.

I have found that each ADO.NET provider uses a slightly different order of the columns and some have more columns than others, but the only ones I'm interested in are: ColumnName, DataType, ProviderType, ColumnSize, and AllowDBNull. ProviderType needs to be parsed into an appropriate enumeration (e.g. System.Data.SqlDbType when using SQL Server) to get a meaningful value.

An additional benefit of this technique is that you can list out the columns of a view or ad hoc SQL as well, not just tables.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Software Developer (Senior)
United States United States
BSCS 1992 Wentworth Institute of Technology

Originally from the Boston (MA) area. Lived in SoCal for a while. Now in the Phoenix (AZ) area.

OpenVMS enthusiast, ISO 8601 evangelist, photographer, opinionated SOB, acknowledged pedant and contrarian


"Using fewer technologies is better than using more." -- Rico Mariani

"Good code is its own best documentation. As you’re about to add a comment, ask yourself, ‘How can I improve the code so that this comment isn’t needed?’" -- Steve McConnell

"Every time you write a comment, you should grimace and feel the failure of your ability of expression." -- Unknown

"If you need help knowing what to think, let me know and I'll tell you." -- Jeffrey Snover [MSFT]

"Typing is no substitute for thinking." -- R.W. Hamming

"I find it appalling that you can become a programmer with less training than it takes to become a plumber." -- Bjarne Stroustrup

ZagNut’s Law: Arrogance is inversely proportional to ability.

"Well blow me sideways with a plastic marionette. I've just learned something new - and if I could award you a 100 for that post I would. Way to go you keyboard lovegod you." -- Pete O'Hanlon

"linq'ish" sounds like "inept" in German -- Andreas Gieriet

"Things would be different if I ran the zoo." -- Dr. Seuss

"Wrong is evil, and it must be defeated." –- Jeff Ello

"A good designer must rely on experience, on precise, logical thinking, and on pedantic exactness." -- Nigel Shaw

“It’s always easier to do it the hard way.” -- Blackhart

“If Unix wasn’t so bad that you can’t give it away, Bill Gates would never have succeeded in selling Windows.” -- Blackhart

"Omit needless local variables." -- Strunk... had he taught programming

You may also be interested in...

Comments and Discussions

-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.160721.1 | Last Updated 3 May 2012
Article Copyright 2012 by PIEBALDconsult
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid