I'm trying to write a sql query to get all the fields in a given item in Sitecore.
To say I am stuck is putting it mildly.
I'm guessing I have to do some self joining on the fields table, but I'm getting myself in knots.
Anyone have any ideas?
In none of the cases you should ever try to query the Sitecore database yourself. The database changes over time and this would break your code. Rather, use the
Item.Fields. This is a collection which contains all the necessary fields. If you want to make sure that all the fields are loaded(really loaded, not lazy loaded), than you can use Item.Fields.ReadAll().
Edit: Also, keep in mind that querying doesn't allow you to construct an Item, so you miss the behavior of default values and do not use the intelligent Sitecore caching at all.