1

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

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 
  • I receive this error sometimes, does not work in some views in database, Msg 537, Level 16, State 3, Line 1 Invalid length parameter passed to the LEFT or SUBSTRING function. – user172334 Mar 3 at 5:22
  • 1
    only works sometimes, will keep searching, testing, thanks – user172334 Mar 3 at 23:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy