Enforce Schema-Bound Views in SQL Server

sql serversql-server-2012

For reporting purposes, I need to be able to query dependencies between views and underlying tables at column level (via sys.sql_expression_dependencies).

One way to get SQL Server to store referenced columns is to use schemabound views. As some of my users will create views too, I would like to enforce those. As far as I've seen it isn't possible to allow only views with schemabinding to be created within a SQL Server database though (although this seems a legit requirement to me).

Are there other ways to force SQL Server to keep track of which columns are being referenced by which views? Or is there a hidden way to enforce schemabound views in SQL Server?

Best Answer

You could use a DDL trigger for this. Unfortunately SCHEMABINDING is not an attribute in the EVENTDATA structure, so you have to construct the object name and check a property using that:

CREATE TRIGGER EnforceViewBinding
ON DATABASE 
FOR CREATE_VIEW, ALTER_VIEW 
AS
    DECLARE @xml xml = EVENTDATA();
    DECLARE
        @Name sysname =
            QUOTENAME(@xml.value('(./EVENT_INSTANCE/SchemaName)[1]', 'sysname')) + 
            N'.' +
            QUOTENAME(@xml.value('(./EVENT_INSTANCE/ObjectName)[1]', 'sysname'));

    IF OBJECTPROPERTYEX(OBJECT_ID(@Name, N'V'), 'IsSchemaBound') = 0
    BEGIN
        RAISERROR ('Views must specify the WITH SCHEMABINDING option.', 16, 1);
        ROLLBACK;
    END;
Related Question