SQL视图中的动态模式名称

Question

我有两个数据集：

< li >一个是关于狗的数据[我的数据] < li >第二个是匹配关键字的查找表[我无法控制该数据]

匹配键会定期更新，我想创建 Dog 数据集的视图（或实现相同目的的视图），该视图始终联接最新的匹配键。此外，我需要能够内联引用它 - 就好像它是一个表格一样。

查找表中的匹配更新通过其模式名称来区分，因此要获取最新的，我只需识别最新的模式名称并将其从查询中交换出来。

假设视图和表值函数都禁止动态SQL，并且存储过程不能像表那样被引用，那么我如何仅用SQL来实现这一点呢？

Answer 1

查找表中的匹配更新根据它们的模式名称进行区分，所以为了获得最新的模式名称，我只需要确定最新的架构名称并将其替换出查询。

您可以使用视图来解决这个问题，但每当向数据库中输入新数据时，都需要某种方法来更改它。

我假设每当创建新模式时，也会在该模式中创建一个新表，但表名和列名总是相同的。请注意，这个假设对我即将提出的解决方案至关重要，该解决方案是使用DDL触发器在数据库级别监听create_table事件来更改视图，以便它引用新创建的表的模式。

我做的另一个假设是，您要么已经有了初始视图，要么正在使用SQL Server 2016或更高版本(允许创建或更改语法)。

首先，让我们创建初始视图：

CREATE VIEW dbo.TheView 
AS 
    SELECT NULL As Test 
GO

然后，我添加了DML触发器，它根据新创建表的模式创建并执行动态更改视图语句：

CREATE TRIGGER AlterViewWhenSchemaChanges
    ON DATABASE
    FOR CREATE_TABLE
AS

    DECLARE @Sql nvarchar(max),
            @NewTableName sysname,
            @NewSchemaName sysname;

    SELECT  @NewSchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'),
            @NewTableName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)');

    -- We only want to alter the view when this specific table is created!
    IF @NewTableName = 'TableName' 
    BEGIN 

        SELECT @Sql = 
        'ALTER VIEW dbo.TheView
        AS
            SELECT Col as test
            FROM '+ @NewSchemaName +'.'+ @NewTableName

        EXEC(@Sql)
    END
GO

这样，每当创建一个具有特定名称的新表(在我的示例中为< code>TableName)时，视图就会被修改为引用最后创建的< code>TableName(显然是在最新的模式中创建的)。

测试脚本：

SELECT * FROM dbo.TheView;
GO

结果：

Test
NULL

使用表< code>TableName创建新的架构

CREATE SCHEMA SchemaName
CREATE TABLE SchemaName.TableName (Col int);

GO  

-- insert some data
INSERT INTO SchemaName.TableName(Col) VALUES (123);

-- get the data from the altered view
SELECT * FROM dbo.TheView

结果：

test
123

你可以在Rextester上看到一个现场演示。