getFieldnames()

[GIGN]

Sorry about this, this is a general sql question. What I want to know, and haven't been able to find, is how to get the field names from a table.

I am writing an online DBAdministrator, and would like to be able to obtain these to keep the code as portable as possible.

Is there any wa to do this?

TIA - BJ ;-)

 

[tlbroadbent]

In SQL terminology, fields are called columns. SQL Server stores column information in the syscolumns table of each database. Microsoft also developed views and stored procedures which provide schema information.

Use the information_schema.columns view to get info about columns in the database.

Select * From dbname.information_schema.columns

The sp_help stored procedure also provides info about columns. The following example would list the column names and attributes for a table called Employees in the current database.

exec sp_help 'dbo.Employees' Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[GIGN]

You mean tablename right?

Select * From tableName.information_schema.columns

Well anyway, I have tried using this,I am testing using ASP, and PWS with Jet4.0. I get unexpected results which you may find interesting

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] Could not find file 'C:\PROGRAM FILES\Norton AntiVirus\dynamic_elements.information_schema'.

/Appliance_Repairs/index.asp, line 76


weird huh? similar result when I use table names, what do you reckon is going on?

Bj

 

[tlbroadbent]

No, I meant database name (dbname). The view is defined in the database not a table. However, this only applies to SQL Server not Access. If you are seeking this information from Access then you'll need to use Access syntax. I suggest posting the question in an Access forum such as forum701.

BTW: How are you establishing the connection to the database? When you post the question in the Access forum, perhaps you could also post the ASP code you use to connect and query the database. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.