Sql-server – SQL Find Code for Each Separate Column in View

sql serversql-server-2016view

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 '%%'

Best Answer

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

enter image description here

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
Related Question