Dynamic Schema name in SQL View

Question

I have two datasets:

1. one is data about dogs [my data]
2. the second is a lookup table of matching keys [I have no control over this data]

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.

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?

Answer 1

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.

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.

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

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

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).

Testing the script:

SELECT * FROM dbo.TheView;
GO

Results:

Test
NULL

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

Results:

test
123

You can see a live demo on Rextester.