Microsoft SQL Server
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 



Go Back   Dev Articles Community ForumsDatabasesMicrosoft SQL Server

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old March 31st, 2003, 03:01 AM
Jacks Jacks is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Mar 2003
Posts: 12 Jacks User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Question 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

Reply With Quote
  #2  
Old March 31st, 2003, 03:52 AM
stumpy's Avatar
stumpy stumpy is offline
May contain nuts.
Dev Articles Regular (2000 - 2499 posts)
 
Join Date: Aug 2002
Posts: 2,056 stumpy User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 5 h 44 m 22 sec
Reputation Power: 0
Well, it all depends on what database and language you are using.

For ASP, here's a little bit of code i use:
Code:
for each f in RS.Fields
   Response.Write(f.Name & "<br>")
next
where RS is a recordset

Reply With Quote
  #3  
Old March 31st, 2003, 04:17 AM
Jacks Jacks is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Mar 2003
Posts: 12 Jacks User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
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!

Reply With Quote
  #4  
Old March 31st, 2003, 04:26 AM
stumpy's Avatar
stumpy stumpy is offline
May contain nuts.
Dev Articles Regular (2000 - 2499 posts)
 
Join Date: Aug 2002
Posts: 2,056 stumpy User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 5 h 44 m 22 sec
Reputation Power: 0
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!

Reply With Quote
  #5  
Old March 31st, 2003, 04:31 AM
Jacks Jacks is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Mar 2003
Posts: 12 Jacks User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
You're a star!!!

Thats just what i want...thanks!


Reply With Quote
  #6  
Old May 17th, 2004, 01:08 AM
gigabandit gigabandit is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: May 2004
Posts: 1 gigabandit User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
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

Reply With Quote
  #7  
Old May 17th, 2004, 01:39 AM
stumpy's Avatar
stumpy stumpy is offline
May contain nuts.
Dev Articles Regular (2000 - 2499 posts)
 
Join Date: Aug 2002
Posts: 2,056 stumpy User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 5 h 44 m 22 sec
Reputation Power: 0
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.

Reply With Quote
  #8  
Old September 15th, 2006, 05:31 AM
harihararaman harihararaman is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Sep 2006
Location: India
Posts: 1 harihararaman User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 32 m 49 sec
Reputation Power: 0
Send a message via AIM to harihararaman
Hi there
try this command: SP_COLUMNS <tablename>
you will get database name, owner info, table name, column names, data type, size etc...

Reply With Quote
  #9  
Old January 31st, 2007, 11:08 AM
amritsandhu amritsandhu is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jan 2007
Posts: 1 amritsandhu User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 m 36 sec
Reputation Power: 0
Smile

Try this

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

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsDatabasesMicrosoft SQL Server > List field names from a table


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.

© 2003-2014 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap