List field names from a table

Hi

I'd like to be able to list all the fields in a table. I have a table whose fields can be added/deleted by the user via a web site and i'd like to be able to dynamically list the existing fields but i cant think how to do it.

Can anyone give me an idea?

Thanks

Well, it all depends on what database and language you are using.

For ASP, here's a little bit of code i use:
where RS is a recordset

sorry, i forgot to put that in. I'm using MSSQL and asp. I also need to be able to list the field names even if there is no record in the table.

Could you just clarify what 'f' is in your code? I've got a recordset selecting all records in the table. How do i get the field name to display and not the field contents?

Thanks for your help! I do appreciate it as this is really bugging me!

By MSSQL, I'm assuming you mean SQL Server?

In my code, 'f' is the variable name for an element of the rs.fields collection (object). It's value gets assigned in the FOR loop automatically.

And, yeah, it won't work if there's no records in the table, so, i use this stored procedure: sp_columns

That should do it!

You're a star!!!

Thats just what i want...thanks!

Stumpy,
Is it possible you could display the stored procedure code to show table fields names without content and how to retrieve the info in an ASP page?
Thanks

Well, the core stuff I use is posted above - there's not much to it. The stored proc I use is built into SQLServer - called sp_columns - it returns a recordset of the field names (i.e. the columns) in a specified table. Here's the doco for it: http://msdn.microsoft.com/library/d..._ca-cz_4g37.asp

Test this proc out in SQLServer Enterprise Manager. Use the simple ASP looper script above to see the field names.

Hi there
try this command: SP_COLUMNS <tablename>
you will get database name, owner info, table name, column names, data type, size etc...

Try this

SELECT [name] AS [Column name]
FROM syscolumns
WHERE id = (SELECT id
FROM sysobjects
WHERE type = 'U'
AND [NAME] = 'your_table_name')