Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

T-SQL - Check if view is SCHEMABINDING

I tried to google it, but didn't found an answer...

Is it possible to check if view is created with SCHEMABINDING?

like image 976
Alex Dn Avatar asked May 06 '13 14:05

Alex Dn


People also ask

What is Schemabinding in SQL views?

SCHEMABINDING. Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition.

How do I find the base table of a view in SQL?

To find all of the SQL Server database views where a table is used, just apply a filter criteria on table_name column of the information schema view INFORMATION_SCHEMA.

What does the view defined using the WITH Schemabinding option accomplishes?

In SQL Server, when we use the “WITH SCHEMABINDING” clause in the definition of an object (view or function), we bind the object to the schema of all the underlying tables and views. This means that the underlying tables and views cannot be modified in a way that would affect the definition of the schema-bound object.


2 Answers

You've already accepted another answer, but the OBJECTPROPERTY() function can answer this directly:

select objectproperty(object_id('viewname'), 'IsSchemaBound')

Note also that sys.sql_dependencies is deprecated.

like image 162
Pondlife Avatar answered Sep 29 '22 18:09

Pondlife


I'm not aware of a direct way, but you could run

select * 
from sys.sql_dependencies
where class = 1 and object_id = object_id('<view name>');

If it returns values, the view is bound.

like image 39
Alexey A. Avatar answered Sep 29 '22 17:09

Alexey A.