Início / dba / Perguntas / 54893
Accepted
vstrien
Asked: 2013-12-13 04:46:13 +0800 CST

Aplicar exibições vinculadas ao esquema

  • 772

Para fins de relatório, preciso poder consultar dependências entre exibições e tabelas subjacentes no nível da coluna (via sys.sql_expression_dependencies).

Uma maneira de fazer com que o SQL Server armazene colunas referenciadas é usar exibições com limite de esquema. Como alguns de meus usuários também criarão exibições, gostaria de aplicá-las. Tanto quanto eu vi, não é possível permitir que apenas visualizações com schemabinding sejam criadas dentro de um banco de dados do SQL Server (embora isso pareça um requisito legítimo para mim).

Existem outras maneiras de forçar o SQL Server a controlar quais colunas estão sendo referenciadas por quais exibições? Ou existe uma maneira oculta de impor exibições de schemabound no SQL Server?

sql-server sql-server-2012

3 respostas

  1. Muito fácil de fazer com um gatilho DDL em seu banco de dados:

    CREATE TRIGGER RequireSchemaBinding ON DATABASE FOR CREATE_VIEW, ALTER_VIEW
AS
DECLARE @object nvarchar(max)
DECLARE @schema nvarchar(max)
SET @object = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(max)')
SET @schema = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(max)')
IF ISNULL(OBJECTPROPERTY(OBJECT_ID(QUOTENAME(@schema) + '.' + QUOTENAME(@object)), 'IsSchemaBound'), 0) = 0
BEGIN
    RAISERROR('Not today, punk.', 16, 1)
    ROLLBACK TRANSACTION
END
    • 7
  2. Best Answer

    Você pode usar um gatilho DDL para isso. Infelizmente SCHEMABINDINGnão é um atributo na EVENTDATAestrutura, então você tem que construir o nome do objeto e verificar uma propriedade usando isso:

    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;
    • 7

  3. Outro método seria usar uma política. Criei uma política de exemplo usando o seguinte script gerado pelo SSMS e funcionou.

    Declare @object_set_id int
EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N'SchemaBinding_Required_ObjectSet', @facet=N'IViewOptions', @object_set_id=@object_set_id OUTPUT
Select @object_set_id

Declare @target_set_id int
EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'SchemaBinding_Required_ObjectSet', @type_skeleton=N'Server/Database/View', @type=N'VIEW', @enabled=True, @target_set_id=@target_set_id OUTPUT
Select @target_set_id

EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database/View', @level_name=N'View', @condition_name=N'', @target_set_level_id=0
EXEC msdb.dbo.sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N'Server/Database', @level_name=N'Database', @condition_name=N'', @target_set_level_id=0
GO

Declare @policy_id int
EXEC msdb.dbo.sp_syspolicy_add_policy @name=N'SchemaBinding_Required', @condition_name=N'SchemaBound_Required', @policy_category=N'', @description=N'', @help_text=N'', @help_link=N'', @schedule_uid=N'00000000-0000-0000-0000-000000000000', @execution_mode=1, @is_enabled=True, @policy_id=@policy_id OUTPUT, @root_condition_name=N'', @object_set=N'SchemaBinding_Required_ObjectSet'
Select @policy_id
GO

    Claro, meu entendimento é que ele é aplicado usando gatilhos de qualquer maneira, mas pode ser mais fácil gerenciar dessa maneira. Além disso, se você tiver várias instâncias, poderá usar um CMS (Central Management Server) para impor a política em todas ou algumas de suas instâncias.

    • 3

relate perguntas