Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to get column name from a table?

Author  Topic 

cfoster
Starting Member

7 Posts

Posted - 2006-04-11 : 20:10:08
Hi,

I've created a simple MS-Access based database for a client. I don't have access to the database except through ASP/SQL.

I needed to add a new column to a table and that was fine. But I must have mistyped the column name because if I try to select it, I can't. I can 'select *' and see that there is a new column full of null values, but I can't select it by name.

I have since added a column with the correct name and that seems to be working fine, but I would like to clean up this other column without bothering them.

Is there a way to get a description of a table via SQL if the database is just MS-Access?

I have tried MANY variations to see if I could get something to work, but so far I usually just get the error:

"Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'."

which leads me to believe there is no way to do it, unless its in a stored procedure that I don't know about.

Any thoughts on the matter would be greatly appreciated!

Thanks,
Colin.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-11 : 21:02:13
try
select [your column name] from yourtable




KH


Go to Top of Page

cfoster
Starting Member

7 Posts

Posted - 2006-04-11 : 21:33:21
quote:
Originally posted by khtan

try
select [your column name] from yourtable




Unfortunately it's the 'your column name' that I don't have.

If there was a way to refer to a column by number such as

ALTER TABLE my_table DROP COLUMN 4

that would also work for me. But if not, I need some way to find out what the column name actually is.

Thanks,
Colin.
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-11 : 21:37:45
but you can see the name of the column?

your problem is you can't drop that column? but can you drop other columns?


--------------------
keeping it simple...
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-11 : 21:49:03
use sp_help yourtable and see weather the column is there



KH


Go to Top of Page

cfoster
Starting Member

7 Posts

Posted - 2006-04-11 : 23:42:50
Hi Jen:
I created a column and I no longer know its name, so I would like to find some way to drop that column. I think I need to know its name to do that, but I don't know how to find the column names of a table in MS-SQL. (I can do it in mySQL with DESCRIBE, so I guess I'm looking for an equivalent to that.)


Khtan:
Thanks for the suggestions!

I just tried
procedure sp_help my_table_name

but got back
"Microsoft JET Database Engine (0x80040E14)
Syntax error in PARAMETER clause."

Perhaps that stored procedure isn't supported by MS-Access?

Can you think of anything else I can try? (Or was my syntax incorrect?)

Thanks,
Colin.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-11 : 23:50:37
Oh MS Access. Sorry

try
select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = my_table_name





KH


Go to Top of Page

cfoster
Starting Member

7 Posts

Posted - 2006-04-11 : 23:59:13

Hmmm... The error I get now is:
Could not find file 'C:\WINNT\system32\INFORMATION_SCHEMA.mdb'.

Does this mean their server isn't quite setup correctly? (It's certainly possible.)

-Colin.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-12 : 00:18:49
MS Access might not support INFORMATION_SCHEMA

Doesn't select * from my_table_name gives you the column name ?



KH


Go to Top of Page

cfoster
Starting Member

7 Posts

Posted - 2006-04-12 : 00:23:44
Sadly, no. 'Select *' returns just the data.

The fact that a path is given to the target file makes me think MS-Access knows what to do with that command, but is missing a file to do it. (But I could be wrong, it just seems odd that MS-Access would know the path of a service it doesn't have, if you see what I mean.)

Well, I guess I'm stuck, unless there are any other suggestions?

Thanks very much for all the feedback!
-Colin.
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-12 : 02:15:46
What's your front end?

usually if do it in vb, and save the recordset in a an ado, the object will have the column names...

not sure what you are using, there must be a way to identify the column unless it's array type...



--------------------
keeping it simple...
Go to Top of Page

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-12 : 08:25:10
Try in ASP
(U may need to define the record set, i and a connection object and connect to the DB, pass the
Select top 0 * from ur_table

and then do the following)

For i = 0 To rs.RecordCount - 1
rs.fields (i)
Next


Srinika
Go to Top of Page

cfoster
Starting Member

7 Posts

Posted - 2006-04-12 : 09:06:54
Jen:
The front end is ASP/Javascript, but I could just as easily write something in ASP/VBScript.

Currently I return the result in 'query_result' and loop through its .Fields(i) like so:
	rec_count = g_query_result.Fields.Count - 1;

while( !g_query_result.EOF )
{
for( i = 0; i <= rec_count; i++ )
{
one_field = g_query_result.Fields(i);
Response.Write( one_field + " " );
}

Response.Write( "<br />" );
g_query_result.MoveNext;
}

-------------
Srinika:
I get an error when I try to select top 0, but I'll see what I can do in in VBScript since it handles objects differently.

[time passes]

Success! Thanks! Here's a code snippet to spill one's table's column names:
<%@ Language="vbscript" %>
<%
Dim objConn
Dim objRec

Set objConn = Server.CreateObject("ADODB.Connection")
Set objRec = Server.CreateObject("ADODB.RecordSet")

objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; data source=PATH_TO_MS_ACCESS_DATABASE"
objRec.Open "MY_TABLE_NAME", objConn, 0, 1, 2

For Each fldF In objRec.Fields
Response.Write fldF.Name
Response.Write "<br />"
Next

objRec.Close
objConn.Close
Set objRec = Nothing
Set objConn = Nothing
%>


It turned out I originally created the column with the name "f_account_type" but thought it was "f_user_type".

Thanks again to everyone for their assistance!
-Colin.
Go to Top of Page

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-12 : 09:15:49
quote:
Originally posted by cfoster


Srinika:
I get an error when I try to select top 0, but I'll see what I can do in in VBScript since it handles objects differently.




The reason is not the way VB Script handles it, but "top 0" is not understood by "Access"
Following should work;

<%@ Language="vbscript" %>
<%
Dim objConn
Dim objRec

Set objConn = Server.CreateObject("ADODB.Connection")
Set objRec = Server.CreateObject("ADODB.RecordSet")

objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; data source=PATH_TO_MS_ACCESS_DATABASE"
objRec.Open "Select Top 1 * from MY_TABLE_NAME", objConn

For Each fldF In objRec.Fields
Response.Write fldF.Name
Response.Write "<br />"
Next

objRec.Close
objConn.Close
Set objRec = Nothing
Set objConn = Nothing
%>




Srinika
Go to Top of Page

cfoster
Starting Member

7 Posts

Posted - 2006-04-12 : 09:27:34
quote:
Originally posted by Srinika
objRec.Open "Select Top 1 * from MY_TABLE_NAME", objConn, 0, 1, 2



Hmmm... oddly enough that doesn't work. I get a syntax error. Yet if I copy/paste the same query into my javascript version, it works fine (but selects the first record, not the column headings).

Anyway, the way I had it with just stating the table name seems to work fine.

Thanks again!
-Colin.

EDIT: Sorry, I didn't notice you dropped the "0, 1, 2" from the end. It does work just as well. (I have no idea what those numbers specify, actually -- my code seems to run fine without them, too).

Go to Top of Page

wjbarton
Starting Member

3 Posts

Posted - 2007-02-01 : 18:24:50
This works for SQL server, borrowed code from sp_help. Change the second line to reflect the column name you're looking for:

declare @tblname varchar(100)
select @tblName = 'tblUsers'

declare @objid int
declare @sysobj_type char(2)

select @objid = id, @sysobj_type = xtype from sysobjects where id = object_id(@tblName)

select name from syscolumns where id = @objid
Go to Top of Page

wjbarton
Starting Member

3 Posts

Posted - 2007-02-01 : 18:25:49
On the above post I meant change the second line to reflcet the name of the table you're trying to get column names for...
Go to Top of Page

wjbarton
Starting Member

3 Posts

Posted - 2007-02-01 : 18:30:13
Changed the last line to sort properly (was sorting by name by default), not sure what 'number = 0' is for, grabbed it from sp_help also:

select name from syscolumns where id = @objid and number = 0 order by colid
Go to Top of Page

Anthraxx
Starting Member

1 Post

Posted - 2009-08-11 : 13:58:45
Use the following code to get the names of all columns in a table:

Dim oConn As New System.Data.OleDb.OleDbConnection
Dim oCmd As New System.Data.OleDb.OleDbCommand
Dim iClm As Integer

oCmd.Connection = oConnection

~~Code to connect to the database~~

With oCmd
.CommandText = "SELECT * FROM TABLE_NAME"
With .ExecuteReader
'Get the column names
For iClm = 0 To .FieldCount - 1
'To get the name use:
'.GetName(iClm)
'For example:
lstColumns.Items.Add(.GetName(iClm))
Next

'Close the reader
.Close()
End With
End With


Greetz,
Anthraxx
Go to Top of Page
   

- Advertisement -