CODEWITHSUNDEEP
sqlsql-server-2012viewmetadatadatabase-metadata
shelli
shelli

Reputation: 1

What is the source column of each column in view

How can I know for each column in view, or procedure the source column
For example, I have this table:

CREATE TABLE [dbo].[tbl_Address](
    [iAddressId] [int] IDENTITY(1,1) NOT NULL,
    [nvStreet] [nvarchar](50) NULL,
    [iHouseNum] [int] NULL,
    [nvEnterance] [nvarchar](10) NULL,
    [iFloor] [int] NULL,
    [nvNeighberwood] [nvarchar](20) NULL,
    [nvCity] [nvarchar](20) NULL,
    [nvNote] [nvarchar](50) NULL,
 CONSTRAINT [PK_tbl_Address] PRIMARY KEY CLUSTERED 
(
    [iAddressId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

And this view:

CREATE VIEW ViewALIAS
AS
SELECT a.nvStreet myStreet,a.nvCity manyStreeats FROM tbl_Address a

The required answer: 

nameObject | nameColumn | SourceObject | SourceColumn  
----------------------------------------------------- 
tbl_Address| nvStreet   | ViewALIAS    | myStreet  
tbl_Address| nvCity     | ViewALIAS    | manyStreeats

I know how to make the columns: nameObject, nameColumn, SourceObject or the table: nameObject, SourceObject, SourceColumn
But I do not know how to match x and t

Upvotes: 0

Views: 1266

Answers (1)

Alberto Martinez
Alberto Martinez

Reputation: 2670

I think that you can't get exactly what you want (see explanation below) but you can get an approximation, first you can get all the columns of the view using:

select name, column_id from sys.columns where object_id=object_id('ViewALIAS')

and then you can get the columns referenced by the view using column dependencies, note that this include all the columns used in any part of the query, not only columns in the select list (for example it would list a column used in a WHERE condition):

select dependencies.referenced_entity_name as referenced_name,
  entities.referenced_minor_id as column_id, entities.referenced_minor_name as column_name
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_id!=0
  and referencing_id=object_id('ViewALIAS')


You can't link the two results because as far as I know there is no way in SQL Server of getting the relation between view columns and referenced columns in the object catalog views, I don't know the exact reason but I'm quite sure that the primary cause is that a view column could be almost anything, so it's not always possible match it to a table column, a few examples:

CREATE VIEW ExampleView AS
SELECT ID, -- table column
  (Value1+Value2)*Value3 as Total, -- multiple columns
  12345 as Value, -- literal value
  getdate() as CurrentDate, -- scalar function
  dbo.fnMyFunction(Value1,Value2) as ScalarColumn, -- scalar function with parameters
  TableFunctionColumn, -- column from a table function (which can be almost anything)
  CASE WHEN Value1<10000 THEN Value2
    WHEN Value1>20000 THEN Value3
    ELSE Value2+Value3 END AS CaseColumn, -- different columns based on another value
  (SELECT SUM(OtherValue) FROM OtherTable
    WHERE OtherID=ID GROUP BY OtherValue) AS SubqueryColumn -- subquery result
FROM ExampleTable
CROSS APPLY dbo.fnTableFunction(ID)

I think the CASE column is the most representative example because the table columns used by the view column depends on the value of another column which is not used directly for the column value.

Upvotes: 1

Related Questions