SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to get column name from a table?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cfoster
Starting Member

Canada
7 Posts

Posted - 04/11/2006 :  20:10:08  Show Profile  Reply with Quote
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)

Singapore
17642 Posts

Posted - 04/11/2006 :  21:02:13  Show Profile  Reply with Quote
try
select [your column name] from yourtable




KH


Go to Top of Page

cfoster
Starting Member

Canada
7 Posts

Posted - 04/11/2006 :  21:33:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 04/11/2006 :  21:37:45  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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)

Singapore
17642 Posts

Posted - 04/11/2006 :  21:49:03  Show Profile  Reply with Quote
use sp_help yourtable and see weather the column is there



KH


Go to Top of Page

cfoster
Starting Member

Canada
7 Posts

Posted - 04/11/2006 :  23:42:50  Show Profile  Reply with Quote
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.

Edited by - cfoster on 04/11/2006 23:50:53
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 04/11/2006 :  23:50:37  Show Profile  Reply with Quote
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

Canada
7 Posts

Posted - 04/11/2006 :  23:59:13  Show Profile  Reply with Quote

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)

Singapore
17642 Posts

Posted - 04/12/2006 :  00:18:49  Show Profile  Reply with Quote
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

Canada
7 Posts

Posted - 04/12/2006 :  00:23:44  Show Profile  Reply with Quote
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.

Edited by - cfoster on 04/12/2006 00:27:04
Go to Top of Page

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 04/12/2006 :  02:15:46  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

Sri Lanka
1378 Posts

Posted - 04/12/2006 :  08:25:10  Show Profile  Reply with Quote
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

Canada
7 Posts

Posted - 04/12/2006 :  09:06:54  Show Profile  Reply with Quote
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.

Edited by - cfoster on 04/12/2006 09:08:06
Go to Top of Page

Srinika
Flowing Fount of Yak Knowledge

Sri Lanka
1378 Posts

Posted - 04/12/2006 :  09:15:49  Show Profile  Reply with Quote
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

Canada
7 Posts

Posted - 04/12/2006 :  09:27:34  Show Profile  Reply with Quote
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).


Edited by - cfoster on 04/12/2006 09:42:00
Go to Top of Page

wjbarton
Starting Member

3 Posts

Posted - 02/01/2007 :  18:24:50  Show Profile  Reply with Quote
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 - 02/01/2007 :  18:25:49  Show Profile  Reply with Quote
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 - 02/01/2007 :  18:30:13  Show Profile  Reply with Quote
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 Posts

Posted - 08/11/2009 :  13:58:45  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000