SQL Find Code for Each Separate Column in View We have a SQL View, trying to find the calculation/business logic for each column into a select or table.

create dbo.CustomerVw
as 
   FirstName + ' ' LastName as FullName
   datediff(yy,BirthDate,GETDATE()) as Age,
   'New York' as CityReason
from dbo.Customer


Expected Results:

Row 1: FullName: FirstName + ' ' + LastName
Row 2: Age: datediff(yy,BirthDate,GETDATE())
Row 3: City: New York


Code below only displays table code as large text. How would separately display each calculation logic for columns? Each Column row can be a text.

SELECT 
    v.TABLE_NAME, 
    v.VIEW_DEFINITION 
FROM 
    INFORMATION_SCHEMA.VIEWS v 
WHERE 
    v.TABLE_NAME LIKE '%%'
IT will not get you all the way, but you could try string splitting on the new line character (char10):

SELECT  
    value 
FROM 
    INFORMATION_SCHEMA.VIEWS v 
    CROSS APPLY string_split(v.VIEW_DEFINITION,CHAR(10))

WHERE 
   v.TABLE_SCHEMA = 'dbo' and v.TABLE_NAME = 'CustomerVw';


Result





View definition

create view [dbo].[CustomerVw]
as 
 SELECT  FirstName + ' ' +LastName as FullName,
   datediff(yy,BirthDate,GETDATE()) as Age,
   'New York' as CityReason
from dbo.Customer;
GO


some adaptions to get closer to what you want (Not very clean)

SELECT  
value 
FROM 
    INFORMATION_SCHEMA.VIEWS v 
    CROSS APPLY string_split(LEFT(SUBSTRING(v.VIEW_DEFINITION,(charindex('SELECT',v.VIEW_DEFINITION) + 6),len(v.VIEW_DEFINITION))
                            ,(CHARINDEX('FROM',SUBSTRING(v.VIEW_DEFINITION,(charindex('SELECT',v.VIEW_DEFINITION) + 6),len(v.VIEW_DEFINITION)))) - 2),CHAR(10))
WHERE 
   v.TABLE_SCHEMA = 'dbo' and v.TABLE_NAME = 'CustomerVw';


Result

  FirstName + ' ' +LastName as FullName, 
   datediff(yy,BirthDate,GETDATE()) as Age, 
   'New York' as CityReason