CODEWITHSUNDEEP
sqlsql-server
Qwertiy
Qwertiy

Reputation: 21400

Check if view column may contain null

How can I programmatically find out whether view column is nullable or not?

http://sqlfiddle.com/#!18/b43f2

create table tbl
(
  a int not null,
  b int null,
  c int not null,
  d int null
)

create view vw as select
  a,
  b,
  iif(c = 1, null, c) as c,
  coalesce(d, a) as d
from tbl

Upvotes: 0

Views: 389

Answers (2)

Luc
Luc

Reputation: 1491

You can check this by using COLUMNPROPERTY methode from SQL server

SELECT COLUMNPROPERTY(OBJECT_ID('SchemaName.ViewName', 'V'), 'ColumnName', 'AllowsNull');

Replace schemaName.ViewName with the database schema and view name, e.g. dbo.vw and replace ColumnName, e.g. c:

SELECT COLUMNPROPERTY(OBJECT_ID('dbo.v', 'V'), 'c', 'AllowsNull');

Upvotes: 2

Thom A
Thom A

Reputation: 95574

Can you find this out from the sys objects:

SELECT c.[name],
       c.is_nullable
FROM sys.columns c
     JOIN sys.views v ON c.object_id = v.object_id
WHERE v.name = N'vw';

Upvotes: 2

Related Questions