How to handle functions and operators when querying view definitions to show source tables and columns in results in SQL?

Question

I have a case where I need to "unpack" sql view definitions. So that I would be able to see which source tables and columns are in use in the view. I am using SELECT * FROM INFORMATION_SCHEMA.VIEWS to get view definitions and have a query to get source tables and columns in own rows and separate columns.

I encountered a problem when dealing with functions and operators (+,-,*,/). Currently I am stuck with getting a view column with function/operator that includes multiple source columns into their own rows and fields.

Any help would be greatly appreciated, thanks in advance.

Example of what I am dealing with, current results and required/expected results.

SELECT 'COALESCE(HEADER.ID1,HEADER.ID2,0) AS ID' AS q INTO #TEMP

SELECT q,
SUBSTRING(q,CHARINDEX( 'COALESCE(',q,0)+9,CHARINDEX( '.',q,0)-10) AS [First Table],
SUBSTRING(q,CHARINDEX( 'COALESCE(',q,0)+9,CHARINDEX( ',',q,0)-10) AS [First Table And Column],
SUBSTRING(q,CHARINDEX( ',',q,0)+1,(CHARINDEX( ',0)',q,0)-CHARINDEX( ',',q,0))-1)  AS [Second Table And Column],
FROM #TEMP

--DROP TABLE #TEMP

Results from above query

q	First Table	First Table And Column	Second Table And Column
COALESCE(HEADER.ID1,HEADER.ID2,0) AS ID	HEADER	HEADER.ID1	HEADER.ID2

What I am after is:

q	Table	Column
COALESCE(HEADER.ID1,HEADER.ID2,0) AS ID	HEADER	ID1
COALESCE(HEADER.ID1,HEADER.ID2,0) AS ID	HEADER	ID2

Here is the complete query that I have, in which the function/operator handling would be included.

WITH tmp (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, ColumnRow, ViewDefinition) AS
(
   SELECT
       TABLE_CATALOG,
       TABLE_SCHEMA,
       TABLE_NAME,
       LEFT(ViewDefinition, CHARINDEX(CHAR(13)+CHAR(10), ViewDefinition + CHAR(13)+CHAR(10)) + 1),
       STUFF(ViewDefinition, 1, CHARINDEX(CHAR(13)+CHAR(10), ViewDefinition + CHAR(13)+CHAR(10)), '')
   FROM
        (SELECT
           TABLE_CATALOG,
           TABLE_SCHEMA,
           TABLE_NAME,
        SUBSTRING(VIEW_DEFINITION,CHARINDEX('SELECT',VIEW_DEFINITION)+6,CHARINDEX('FROM',VIEW_DEFINITION)-CHARINDEX('SELECT',VIEW_DEFINITION)-18) AS ViewDefinition
        FROM INFORMATION_SCHEMA.VIEWS
        ) ViewData
   UNION all
   SELECT
       TABLE_CATALOG,
       TABLE_SCHEMA,
       TABLE_NAME,
       LEFT(ViewDefinition, CHARINDEX(CHAR(13)+CHAR(10), ViewDefinition + CHAR(13)+CHAR(10)) + 1),
       STUFF(ViewDefinition, 1, CHARINDEX(CHAR(13)+CHAR(10), ViewDefinition + CHAR(13)+CHAR(10)), '')
   FROM tmp
   WHERE
       ViewDefinition > ''
)

SELECT
  TABLE_CATALOG,
  TABLE_SCHEMA,
  TABLE_NAME,
  RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LEFT(ColumnRow,
        CASE WHEN CHARINDEX('.',ColumnRow) = 0 THEN CHARINDEX('.',ColumnRow)  
            WHEN 
                CHARINDEX('CAST(',ColumnRow) > 0 
                OR CHARINDEX('CASE WHEN',ColumnRow) > 0  
                OR CHARINDEX('COALESCE',ColumnRow) > 0 
                OR CHARINDEX('GETDATE',ColumnRow) > 0 
                OR CHARINDEX('SUM(',ColumnRow) > 0 
                OR CHARINDEX('+',ColumnRow) > 0 
                OR CHARINDEX('-',ColumnRow) > 0 
                OR CHARINDEX('*',ColumnRow) > 0 
                OR CHARINDEX('/',ColumnRow) > 0    
            THEN 
                '' 
            ELSE 
                REPLACE(CHARINDEX('.',ColumnRow)-1,CHAR(9),'')
        END
        ),CHAR(13)+CHAR(10), ''),' ', ''),'ISNULL(',''),CHAR(9),''),CHAR(32),''),CHAR(10),''),CHAR(13),''),CHAR(160),''))) AS LeanTableName,
  CASE 
    WHEN 
      CHARINDEX('CAST(',ColumnRow) > 0
      OR CHARINDEX('CASE WHEN',ColumnRow) > 0  
      OR CHARINDEX('COALESCE',ColumnRow) > 0 
      OR CHARINDEX('GETDATE',ColumnRow) > 0 
      OR CHARINDEX('SUM(',ColumnRow) > 0 
      OR CHARINDEX('+',ColumnRow) > 0 
      OR CHARINDEX('-',ColumnRow) > 0 
      OR CHARINDEX('*',ColumnRow) > 0 
      OR CHARINDEX('/',ColumnRow) > 0    
    THEN 
      ''    
    ELSE 
      REPLACE(REPLACE(REPLACE ( 
      LEFT(RIGHT(ColumnRow,LEN(ColumnRow)-CHARINDEX('.',ColumnRow)),
      CHARINDEX(' AS ',RIGHT(ColumnRow,LEN(ColumnRow)-CHARINDEX('.',ColumnRow)-1))), ',''0'')',''),','''')',''), ')', '')
  END AS LeanColumnName,
  CASE
    WHEN 
      CHARINDEX('CAST(',ColumnRow) > 0   
      or CHARINDEX('nvarchar',ColumnRow) > 0  
      or CHARINDEX('decimal',ColumnRow) > 0  
    THEN 
      LTRIM(REPLACE(SUBSTRING(ColumnRow,CHARINDEX(') AS ',ColumnRow)+4,100),',',''))
    ELSE 
      REPLACE(SUBSTRING(ColumnRow,CHARINDEX(' AS ',ColumnRow)+4,100),',','') 
  END AS ColumnName,  
  LTRIM(LEFT( LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(ColumnRow, CHAR(10), CHAR(32)),CHAR(13), CHAR(32)),CHAR(160), CHAR(32)),CHAR(9),CHAR(32)))), LEN( LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(ColumnRow, CHAR(10), CHAR(32)),CHAR(13), CHAR(32)),CHAR(160), CHAR(32)),CHAR(9),CHAR(32))))) - 1)) AS RowValue
FROM tmp
WHERE (ColumnRow LIKE '% AS %' and ColumnRow not like '%--%')
GO

score 1 · Accepted Answer · answered Jul 04 '23 at 13:11

If you are on SQL Server 2016 or newer you can use STRING_SPLIT function to easily split your columns into separate rows. If you are on older version, you can google for some custom split string functions (there are plenty).

Here is a working example with your provided #TEMP table.

WITH CTE_temp AS
(
    SELECT q
    , SUBSTRING(q,CHARINDEX('COALESCE(',q,0)+9, (CHARINDEX( ',0)',q,0))-CHARINDEX( 'COALESCE(',q,0) - 9) as AllColumns
    FROM #TEMP
)
, CTE_Split AS 
(
    SELECT q, AllColumns, value AS table_column
    FROM CTE_temp  
    CROSS APPLY STRING_SPLIT(AllColumns, ',')
)
SELECT *
, LEFT(table_column, CHARINDEX('.', table_column) - 1) AS [Table] -- you can still user SUBSTRING instead of LEFT/RIGHT 
, RIGHT(table_column, CHARINDEX('.', REVERSE(table_column)) - 1) AS [Column] --note reserved words
FROM CTE_Split;

Hi @Nenad ! Thanks you! This works perfectly. After a couple of days I found out what I was really after was this query: `SELECT * FROM sys.dm_sql_referenced_entities('Schema.View', 'OBJECT');` This returns all the tables and columns from a specific view without the need to write a query for all special cases. — Sam, Jul 14 '23 at 07:19

score 0 · Answer 2 · answered Jul 14 '23 at 08:38

Here is the actual query I ended up needing in this case. Returns all source tables and columns from all views.

select DISTINCT  
    dependencies.referenced_entity_name as SourceTable,
    entities.referenced_minor_name as SourceColumn
from sys.sql_expression_dependencies as dependencies
join sys.objects as objects on object_id=referencing_id
join sys.schemas as schemas on schemas.schema_id=objects.schema_id
cross apply sys.dm_sql_referenced_entities(schemas.name+'.'+objects.name,'OBJECT') as entities
where entities.referenced_entity_name=dependencies.referenced_entity_name
  and (is_schema_bound_reference=0 or entities.referenced_minor_id=dependencies.referenced_minor_id)
  AND entities.referenced_minor_name is not null

Here is the original post I found my answer in: SQL Server Column Dependencies without Deprecated SYS View

How to handle functions and operators when querying view definitions to show source tables and columns in results in SQL?

2 Answers2