Can I cross reference columns in a view back to the base table using data in the system catalogs?


  • I am trying to do some cross referencing of the columns in a view back to the table(s) and column(s) it is built over and I am having some difficulty. Take this example:
    CREATE TABLE [dbo].[PeopleTable](
    	[PersonID] [smallint] IDENTITY(1,1) NOT NULL,
    	[LastName] [varchar](50) NOT NULL,
    	[FirstName] [varchar](50) NOT NULL,
    CREATE VIEW [dbo].[PeopleView] WITH schemabinding AS
      FirstName AS First, 
      LastName AS Last,
      FirstName + ' ' + LastName AS FullName,
     FROM dbo.PeopleTable
    CREATE UNIQUE CLUSTERED INDEX [IX_View_PersonID] ON [dbo].[PeopleView] ([PersonID])
    select * from sys.columns where object_id = object_id('PeopleTable')
    select * from sys.columns where object_id = object_id('PeopleView')
    select * from sys.sql_dependencies where object_id = object_id('PeopleView')
    What I want to do is, using the system catalog tables, determine that column 1 of PeopleView is column 3 of PeopleTable, column 2 of PeopleView is column 2 of PeopleTable, column 3 of PeopleView is computed, and column 4 of PeopleView is column 1 of PeopleTable.

    My ultimate goal is to correlate columns in PeopleView back to foreign key constraints (even though there are no foreign key constraints in this example) on PeopleTable, which should be trivial once I can figure out how to match the columns.

    sys.columns and sys.sql_dependencies give me pieces of the puzzle, but nothing puts the puzzle together. sys.sql_dependencies is the closest, it has the columns the view references, but does not tell which view column references which table column.  If only the column_id were filled in with the column_id from the view, that would complete it for me.

    I've been through all the sys. & INFORMATION_SCHEMA. views and have not found anything usefull. I'm hoping that I don't have to take definition from sys.sql_modules and parse the text to cross reference. Doesn't the database engine need a quick way to link view columns back to the source columns?

    It's almost like I need sys.view_columns or something like that...

    As an interesting side question, shouldn't is_computed in sys.columns for FullName of PeopleView be 1, not 0?
    • Moved by Tom PhillipsModerator Saturday, February 06, 2010 4:35 AM TSQL Question (From:SQL Server Database Engine)
    Saturday, February 06, 2010 3:37 AM

All replies

  • Can anyone assist?

    I think the question is similar to correlating the SELECT list members (simple and complex expressions) back to their origins.

    Kalman Toth, SQL Server 2008 & BI Training, OLAP, SSIS, SSRS;
    Wednesday, February 10, 2010 11:22 PM
  • This should work...

    SELECT AS ViewName
    	, AS ViewColumnName
    	, AS ViewSchemaName
    	, sed.referenced_entity_name AS SrcTableName		
    	, AS SrcColumnName
    	, sed.referenced_schema_name AS SrcSchemaName
      FROM sys.objects obj
     INNER JOIN sys.schemas sch
        ON sch.schema_id = obj.schema_id
     INNER JOIN sys.sql_expression_dependencies AS sed
        ON sed.referencing_id = obj.object_id  
     INNER JOIN sys.sql_dependencies AS dep
        ON sed.referencing_id = dep.object_id 
       AND sed.referenced_id = dep.referenced_major_id
     INNER JOIN sys.columns AS scl 
        ON scl.object_id = sed.referenced_id 
       AND dep.referenced_minor_id = scl.column_id 
     INNER JOIN sys.columns AS vcl 
        ON vcl.object_id = sed.referencing_id 
       AND vcl.column_id = dep.referenced_minor_id 
     WHERE = 'Your View'
       AND = 'Your View Schema'

    • Edited by rhholt Monday, November 03, 2014 9:55 PM
    Monday, November 03, 2014 9:54 PM
  • ...
    INNER JOIN sys.columns AS vcl ON vcl.object_id = sed.referencing_id AND vcl.column_id = dep.referenced_minor_id

    Joining on dep.referenced_minor_id here is wrong, because it is not the correct column id.
    You would want to join on dep.column_id or something like that, but that seems to always be 0.
    I did some testing, and the problem is that we never have a "referencing_minor_id", only the major id.

    Thursday, June 11, 2015 12:15 PM