Find Out Whether a Specific Column Exists in a Database Table

This snippet will allow you to check whether a certain column exists in a database table or not. Useful when you're coding against a database which schema you don't fully know. Just change the name of the column you're looking for (nameToCheck), the Connection String and the SQL SELECT statement, the bold items in this snippet.
<%
	Dim oField ' As ADODB.Field
	Dim oRecordset ' As ADODB.Recordset
	Dim oConn ' As ADODB.Connection
	Dim nameToCheck
	Dim nameExists
	
	' The column name you're looking for
	nameToCheck = "ID"
	nameExists = false
	
	' Create connection
	Set oConn = Server.CreateObject("ADODB.Connection")
	oConn.Open "YourConnectionString"
	
	Set oRecordset = oConn.Execute("SELECT * FROM YourTable")
	
	For Each oField In oRecordset.Fields
		If oField.Name = nameToCheck Then
			nameExists = True
			Exit For
		End If
	Next
	
	oRecordset.Close()
	oConn.Close()
	Set oConn = Nothing
	Set oRecordset = Nothing
	
	Response.Write("Field " & nameToCheck & " found: " & nameExists)
%>

Where to Next?

Wonder where to go next? You can read existing comments below or you can post a comment yourself on this article .


Consider making a donation
Please consider making a donation using PayPal. Your donation helps me to pay the bills so I can keep running Imar.Spaanjaars.Com, providing fresh content as often as possible.



Feedback by Other Visitors of Imar.Spaanjaars.Com

On Monday, July 24, 2006 6:49:37 PM Steven said:
I simplified this way:

Function FieldExists(rstTest As ADODB.Recordset, fldName As String) As Boolean
Dim fld As ADODB.Field

For Each fld In rstTest.Fields
    If fld.Name = fldName Then FieldExists = True
Next

Set fld = Nothing

End Function
On Tuesday, July 25, 2006 8:46:14 PM Imar Spaanjaars said:
Hi Steven,

You would still need the code that opens a connection and builds up a Recordset object, right?

Imar
On Tuesday, July 25, 2006 8:57:32 PM Steven said:
Yes, you would still need a valid ADODB recordset using a valid ADODB connection. My function assumes you're already referring to a recordset in your code and need to check if a field exists. If that is the case, you simply pass the open recordset object by reference along with the field you are checking for, and the function returns a boolean stating whether it exists or not.

This function, however, is written in VB, not ASP. So I'm not sure it will work in and ASP environment. But it works great in VB - I'm using it. My example simply builds off of your code. It didn't click for me what I needed to do until I read your code. Thanks for the help!
On Tuesday, July 25, 2006 9:08:53 PM Imar Spaanjaars said:
Hi Steven ,

You can do petty much the same as this example in ASP; only you shouldn't strongly type your objects. Something like this should work:

Function FieldExists(rstTest, fldName)

  Dim fld
  FieldExists = False ' Give it a default value. Good idea in VB too

  For Each fld In rstTest.Fields
      If LCase(fld.Name) = LCase(fldName) Then FieldExists = True
  Next

  Set fld = Nothing

End Function

Notice how I also added LCase in the mix, to avoid casing issues.

Cheers,

Imar
On Friday, October 27, 2006 8:51:13 PM SH said:
I would suggest SELECT TOP 1 * FROM table so the entire table is not returned
On Friday, October 27, 2006 10:44:53 PM Imar Spaanjaars said:
Hi SH,

Yeah, good suggestion. If you know the name of at least one column, you can add a WHERE clasue that is never true to stop returning any data at all:

SELECT TOP 1 * FROM table WHERE Id = -1

would still return the meta data (e.g. column info) but no rows.

Cheers,

Imar
On Friday, January 19, 2007 3:41:03 PM Lee said:
I believe you can just use WHERE 1 = 2 to get an empty recordset, like this:

SELECT TOP 1 * FROM table WHERE 1 = 2

That way you don't need to know the name of any of the fields.

-----
Lee
On Friday, January 19, 2007 6:18:17 PM Imar Spaanjaars said:
Hi Lee,

Yes, good tip. That would indeed work and this way you can check each column without knowing a single name.

Thanks,

Imar
On Monday, March 12, 2007 3:55:47 PM NTulip said:
if you need more information - your code should execute sp_help passing in the name of the object and then you can pull the following column descriptions:

Column Description
----------------------
Column_name, Type, Computed, Length, Prec , Scale, Nullable                           , TrimTrailingBlanks, FixedLenNullInSource, Collation                                                                            

Separately, you can lookup identity column information, indexes, constraint types, and any references

hope this expands the possibilities
On Friday, March 16, 2007 2:35:47 PM Jason Cluggish said:
Another tweak of this function once the field is found is to exit the function.  This will stop the looping through the rest of the fields, once we already know the field exists.


Function FieldExists(rstTest As ADODB.Recordset, fldName As String) As Boolean
Dim fld As ADODB.Field

For Each fld In rstTest.Fields
    If LCase(fld.Name) = LCase(fldName) Then
        FieldExists = True
        Exit Function
    End If
Next

Set fld = Nothing

End Function
On Friday, March 16, 2007 3:03:57 PM Imar Spaanjaars said:
Hi Jason,

My initial code already uses Exit For to break out of the loop when a column is found. Depending on how you call the code, breaking out of the entire function (as in my example) may leave you with an open DB connection.

Cheers,

Imar
On Wednesday, January 30, 2008 5:58:01 AM Shan Khan said:
I think the original code posted is the easiest and well understanding language. Please dont confuse us...

Thanks,.
On Monday, June 18, 2012 6:21:37 AM sujith Kumar said:
thaks alot , it great healpfull to me , it is working fine with little bit of code changing with con string

good
thanks alot

Talk Back! Comment on Imar.Spaanjaars.Com

I am interested in what you have to say about this article. Feel free to post any comments, remarks or questions you may have about this article. The Talk Back feature is not meant for technical questions that are not directly related to this article. So, a post like "Hey, can you tell me how I can upload files to a MySQL database in PHP?" is likely to be removed. Also spam and unrealistic job offers will be deleted immediately.

When you post a comment, you have to provide your name and the comment. Your e-mail address is optional and you only need to provide it if you want me to contact you. It will not be displayed along with your comment. I got sick and tired of the comment spam I was receiving, so I have protected this page with a simple calculation exercise. This means that if you want to leave a comment, you'll need to complete the calculation before you hit the Post Comment button.

If you want to object to a comment made by another visitor, be sure to contact me and I'll look into it ASAP. Don't forget to mention the page link, or the QuickDocId of the document.

For more information about the Talk Back feature, check out this news item.