0

So I have a view that has lots of "AS" stuff defined for different columns, e.g., some view columns simply map to one column in one physical table, some are defined by functions etc:

SELECT
   dbo.LoanDataTable.F123 AS LoanOfficer, 
   dbo.udf_GetChannelTypeValueWithLoanOfficer(dbo.LoanDataTable.F123) AS ChannelType

In our application I want to be able to display on a web page the mapping of the view columns to their "AS" definitions, in a grid with two columns like so:

LoanOfficer     dbo.LoanDataTable.F123
ChannelType     dbo.udf_GetChannelTypeValueWithLoanOfficer(dbo.LoanDataTable.F123)

I have researched on stackoverflow:

Is there a way to retrieve the view definition from a SQL Server using plain ADO?

and

Suggestions on storing view meta in SQL Server 2008

All the first one does is retrieve the full text of the view definition, and no one ever answered the second one.

If I retrieve the full text of the view definition, such as seen at the top of this post, then I still need to somehow parse the text to extract the "AS" components. So my question is: is there a way to easily extract this information from SQL Server 2008 R2 and above, or if not, has someone already written some view-definition-parser code that will do this?

Community
  • 1
  • 1
  • Here's a link to a question about getting view information from yesterday: http://stackoverflow.com/questions/19687112/how-do-i-get-a-list-of-columns-in-a-table-or-view/19688199#19688199. Anything you get back will require some sort of parsing tho. – Andrew Oct 31 '13 at 16:45
  • Not that such view-definition-parser code would be rocket science, but hey, if someone else has already done it, it would just be nice to save the time :) –  Oct 31 '13 at 16:45
  • Thanks Andrew, the info in that post is very helpful in general sense (especially the sp_columns system stored procedure, which is pretty handy), but still doesn't get to the "AS" info in the view definition :) –  Oct 31 '13 at 16:53
  • It wouldn't be rocket science, but it would be pretty close to a functioning lexical analyzer. Consider what you would have to be able to evaluate to show TableName.ColumnName - Aliases, Explicit Joins, Implicit Joins, Subqueries, With, As, Without the As, [] for column names.. I could go on and on. Not quite rocket science, but pretty complicated nevertheless. – Raj More Oct 31 '13 at 17:10
  • Unless I'm missing something, I don't think it would be that hard to get the info that I am after (a simple key-value list). A high-level description of the general algorithm would be: 1) extract what comes after 'SELECT' and before the other valid DDL keywords that can come after the column definitions; 2) split the extracted column definitions by comma; 2) take the splitted chunks and each one has one thing that comes before the ' AS ', and one thing after it; 3) there is the 2 sets of values (note that I don't need to drill down any further into anything that is before the ' AS '). –  Oct 31 '13 at 17:28
  • No, there is no way to *easily* extract this information. – RBarryYoung Oct 31 '13 at 17:28
  • If someone has already spent what would likely be a couple of hours of coding, it seems reasonable to me to check to see if they have. But that's just me :) Also note that the actual code could be in either T-SQL, or in C#.NET, either would be fine for my purposes. –  Oct 31 '13 at 17:32
  • I'm going to leave this open until later today, then if no one responds that they have a solution already, I'll go ahead and whip out the code. Also, if I do that, I will try to do it in T-SQL first (even though it would be easier/quicker in C#.NET) because then the resultant code would be able to be used by others regardless of their application programming HLL. –  Oct 31 '13 at 18:38

1 Answers1

0

This will get you the actual DDL, if that's the first part of what you're looking for:

SELECT OBJECT_DEFINITION (OBJECT_ID('yourSchema.yourViewGoesHere')) AS ObjectDefinition;
Andrew
  • 8,445
  • 3
  • 28
  • 46
  • Thanks, that's the most concise way I've seen to get the full definition as a starting point. –  Oct 31 '13 at 23:29