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
