Stephen, if you're trying to return the column name, know that it's the DB name of the Remedy field. If you're trying to get the column ID, that's in the metatables.
I built (as have many others) a simple ARS form to return the column names and field IDs from a given form, using either the T# or the Form name. If you would find it helpful, let me know, and I'll send it to you offline. If nothing else, you should be able to pull the SQL from the menus for non-ARS use. Rick -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Heider, Stephen Sent: Thursday, May 04, 2006 5:21 AM To: [email protected] Subject: OT: Get Base Column Name From SQL View Definition - SQL Server 2000 The solution will be used with Remedy but it is a general SQL Server question. How can you get the base column name in a SQL View definition from the view field name? For example, CREATE VIEW uvwTest (FieldName1, FieldName2, FieldName3, FieldName4) AS SELECT BaseColumn1, BaseColumn2, CONVERT(VARCHAR(30), BaseColumn3) AS BaseColumn3, BaseColumn4 FROM BaseTable Let's say I need to get the base column name for the FieldName4 view field. My first approach was to determine the position of the field (in this case FieldName4 happens to be the 4th field) and then grab the corresponding base column using the commas as delimiters. However this does not work if there are extra commas [as used in calculated fields]. In this example the CONVERT function adds a comma to list of base columns. I have looked at the SQL Server sysobjects (and related tables) and the INFORMATION_SCHEMA objects, but no luck. I know that SQL Server itself knows how to parse the list in at least two places: When it executes the View and when you use the Design View wizard in SQL Server Enterprise Manager. Any ideas? Thanks. Stephen ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org
