SQL视图中的动态架构名称 [英] Dynamic Schema name in SQL View

问题描述

我有两个数据集：

1. 一个是关于狗的数据[我的数据]


2. 第二个是匹配键的查找表[我无法控制此数据]

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

The matching keys are updated regularly, and I want to create a View (or something that fulfills the same purpose) of the Dog dataset, which always joins on the most recent matching keys. Furthermore, I need to be able to reference it inline - as though it was a table.

查找表中的匹配更新是有区别的根据其架构名称，因此，要获取最新的架构名称，我只需标识最新的架构名称并将其交换出查询即可。

The match updates in the lookup table are differentiated by their schema names, so to get the most recent, I just have to identify the latest schema name and swap it out of the query.

鉴于视图和表值函数都禁止动态SQL，并且不能像表一样引用存储过程，这怎么能仅用SQL来实现呢？ / p>

Given that both Views and Table Valued Functions prohibit dynamic SQL, and Stored Procedures can't be referenced like a table can be how can I achieve this in just SQL?

推荐答案

查找表中的匹配更新因其架构名称而异，因此可以最大程度地利用最近，我只需要标识最新的架构名称并将其交换出查询即可。

The match updates in the lookup table are differentiated by their schema names, so to get the most recent, I just have to identify the latest schema name and swap it out of the query.

您可以使用视图来解决此问题问题，但是每当将新数据输入数据库时​​，您都需要某种更改方法。

You can use a view to solve this problem, but you need some way of altering it whenever new data is entered into the database.

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

I'm assuming that whenever a new schema is created, a new table is also created in that schema, but the table name and it's column names are always the same. Note that this assumption is critical to the solution I'm about to propose - and that solution is to use a DDL trigger listening to the create_table event on the database level to alter your view so that it will reference the schema of the newly created table.

我正在做的另一个假设是您已经具有初始视图，或者您正在工作SQL Server 2016或更高版本（允许创建或更改语法）。

Another assumption I'm making is that you either already have the initial view, or that you are working with SQL Server 2016 or higher (that allows create or alter syntax).

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

So first, let's create the initial view:

CREATE VIEW dbo.TheView 
AS 
    SELECT NULL As Test 
GO

然后，我添加了DML触发器，该触发器根据以下内容创建并执行动态的 alter视图语句：新创建的表的模式：

Then, I've added the DML trigger, which creates and executes a dynamic alter view statement based on the schema of the newly created table:

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

这样，每当新表具有特定名称（在我的示例中为 TableName ）创建后，视图将更改为引用最后创建的 TableName （显然是在最新架构中创建的）。

This way, whenever a new table with the specific name (TableName in my example) is created, the view gets altered to reference the last TableName created (which is obviously created in the newest schema).

测试脚本：

SELECT * FROM dbo.TheView;
GO

结果：

Test
NULL

使用以下命令创建新架构表 TableName

Create a new schema with the table 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上观看实时演示。

这篇关于SQL视图中的动态架构名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！