How can i join sys.columns of view to sys.columns of the table it is referencing in T-SQL?

Question

I am trying to join records in sys.columns for a view, to the records in sys.columns for the table it is referencing, because i need the values of is_nullable, is_computed and default_object_id columns for the columns that are selected in the view.

The sys.columns records for the view have "incorrect" values, which you can observe by running the example queries below:

CREATE TABLE TestTable (
    FieldA int NOT NULL,
    FieldB int DEFAULT (1),
    FieldC as CONVERT(INT, FieldA + FieldB),
    FieldD int NOT NULL
)
GO

CREATE VIEW TestView WITH SCHEMABINDING AS
SELECT  FieldA, FieldC as TestC, FieldB + FieldC as TestD
FROM dbo.TestTable WHERE FieldD = 1
GO

SELECT  OBJECT_NAME(c.object_id) as ViewName, c.name as ColumnName,
        c.is_nullable as Nullable, c.is_computed as Computed,
   cast(CASE WHEN c.default_object_id > 0 THEN 1 ELSE 0 END as bit) as HasDefault
FROM sys.columns c
WHERE object_id = OBJECT_ID('TestTable')
GO

SELECT  OBJECT_NAME(c.object_id) as ViewName, c.name as ColumnName,
        c.is_nullable as Nullable, c.is_computed as Computed,
   cast(CASE WHEN c.default_object_id > 0 THEN 1 ELSE 0 END as bit) as HasDefault
FROM sys.columns c
WHERE object_id = OBJECT_ID('TestView')
GO

I have tried using system views to join on dependencies, but they do not give us information about which column in the view refers to which column in the table:

-- dm_sql_referenced_entities gives us all columns referenced, but all records
-- have referencing_minor_id 0, so we do not know which column refers to what
SELECT * FROM sys.dm_sql_referenced_entities('dbo.TestView', 'OBJECT')
GO

-- sql_dependencies gives us all columns referenced, but all records has
-- column_id 0 so we can not use this either of joining the columns
SELECT * FROM sys.sql_dependencies WHERE object_id = OBJECT_ID('TestView')
GO

-- sql_expression_dependencies just tells us what table we are referencing 
-- if view is not created WITH SCHEMABINDING. If it is, it will return columns,
-- but with referencing_minor_id 0 for all records, so not able use this either
SELECT * FROM sys.sql_expression_dependencies
    WHERE referencing_id = OBJECT_ID('TestView')
GO

This unanswered post on social.msdn submitted by someone seems to be the same issue: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/4ae5869f-bf64-4eef-a952-9ac40c932cd4

I also found this post while researching, but the solution does not work if you have more than one table selected in the view or the number or order of columns do not match: http://stackoverflow.com/a/10054481/1463929 — Sense545, Oct 30 '12 at 07:55
The nullability and default results for querying the view's columns seems correct. The only possible disagreement I can see is in whether the columns are computed. But, in some senses, every column of a view is "computed". — Damien_The_Unbeliever, Oct 30 '12 at 08:05
I'm not arguing whether the results of the quires are correct or not, but i need to know that TestView TestC refers to a computed column, and if say TestD had referred to just FieldB, i would need to know that the column it referred to has a default value. — Sense545, Oct 30 '12 at 08:11
You can understand that I might have gotten that impression when your second paragraph starts "The **sys.columns** records for the view have "incorrect" values" — Damien_The_Unbeliever, Oct 30 '12 at 08:14
Right, "not the wanted values" then. I only care about the table's values. The actual use-case is that i want to take is_nullable, is_computed and default_object_id, and determine if the column is required when inserting a new record. When rendering the client-side we would include a "required" bit to be able to check client-side if a field is required before sending the actual insert command to the server. — Sense545, Oct 30 '12 at 08:19

score 1 · Answer 1 · answered Oct 30 '12 at 09:26

1

You said "i need to know that TestView TestC refers to a computed column". This is not supported by SQL Server 2008 R2 (not sure for 2012 though, but i doubt it).

First you can query sys.columns or INFORMATION_SCHEMA.COLUMNS and you won't find what you want.

If you dig deeper, you will most probably try sys.sql_expression_dependencies and sys.dm_sql_referenced_entities (N'dbo.TestView', N'OBJECT'), but you can find table-column mapping there, not column-column. SQL server stores dependency information by 'high level' object (table, trigger...), not by its details (column). You will find same in sys.sysdepends. As a matter of fact dependency information is in SQL server unreliable.

At last, your only possibility would be to parse the view body by yourself. It can be found in sys.sql_modules:

SELECT m.definition
FROM 
    sys.objects o
    JOIN sys.sql_modules m 
        ON m.object_id = o.object_id
WHERE 
    o.object_id = object_id('dbo.TestView')
    and o.type = 'V'

Parsing T-SQL is VERY hard, it could really push you to the limit of your efforts. For instance, it should be more or less easy to grab table references from the view, and then table columns, especially if your view is schema-bound. But if it's not, well... just think of asterisks that reference OUTER APPLY, which references recursive CTE...

Anyway, good luck!

answered Oct 30 '12 at 09:26

OzrenTkalcecKrznaric

5,535
4
34
57

If you read my whole question, you would see that i have already tried sys.sql_dependencies, sys.sql_expression_dependencies and sys.dm_sql_referenced_entities. "Not supported" and "parse the view body" are not acceptable solutions. – Sense545 Oct 31 '12 at 12:55
Thank you for the feedback. Yes, I read through the whole question and understood everything what you've tried. I just wanted to make the answer more elaborate. Also, I understand why you can't accept my answer as such, but that's just the way it is - not supported. Currently the only possibility is to parse the query :( – OzrenTkalcecKrznaric Oct 31 '12 at 14:14
I will see if i can use Microsoft.SqlServer.Management.SqlParser.dll in a CLR function to parse the definition retrieved from sys.sql_modules, and use the parsed column mappings to get what i want. Will post here again if i get it to work. – Sense545 Oct 31 '12 at 18:11
Wow! If you get that to work, you could be the first one. Or maybe one of the few. I admit that when I needed column dependency I gave up after few days of browsing and digging. Just let the user select details for data type (hint: from dependent table if information is available). – OzrenTkalcecKrznaric Oct 31 '12 at 20:13
I got it working using Microsoft.SqlServer.Management.SqlParser.Scanner, but because of deployment issues to different versions of Sql Server we had to roll back for now. I'll probably be implementing the T-SQL tokenizer my self soon, and use that instead of the Scanner from Microsoft's dll. – Sense545 Apr 12 '13 at 05:52

score 1 · Answer 2 · edited Jun 17 '14 at 23:56

Currently, when views are created there are two operations that happen at a high level - parsing & binding. Parsing is basically checking for syntax of the statement, keywords & such. Binding is the process of mapping the identifiers (object names, column names) in the statement to the corresponding objects (tables, views, functions, columns etc.) & derivation of types. Additionally, in case of view similar to SELECT statements you can optionally alias column references and expressions in the SELECT list or after the view name (ex: create view v1(a) as select i from t).

After binding, we persist only the column aliases & the derived types in the metadata since a view is logically a table derived from a query expression. So there is currently no way to determine the expression that the column aliases map to or what it contains (columns or functions or literals etc.) Only way to obtain the information you are looking for is to parse the view definition & perform your own binding. I believe we already have a bug that tracks the feature request to expose more richer dependency information regarding the mapping of aliases to column expressions in view definitions.

Lastly, SQL Server Developer Studio or Visual Studio Database Project uses the managed T-SQL parser to track such references so you can do refactoring or renaming for example using the project. Hope this helps clarify the problem/current implementation.

Thank you for your response. Do you have a URL to this bug / feature request? — Sense545, Jun 18 '14 at 08:12

score -1 · Answer 3 · answered Jun 11 '15 at 00:00

It seems like the misunderstanding is the assumption that object_id is a primary key in sys.columns. It is not. The object_id in sys.columns relates to the object_id in sys.objects. So:

SELECT C.*
FROM sys.objects T
INNER JOIN sys.columns C
ON T.object_id = C.object_id
WHERE T.type in ('S','U') -- System Tables and User Tables
AND T.name = 'Address' -- Table Name
order by C.Column_ID

will return the columns in the "Address" table in AdventureWorks.

How can i join sys.columns of view to sys.columns of the table it is referencing in T-SQL?

3 Answers3