Dynamic Schema name in SQL View

Posted on

Dynamic Schema name in SQL View – This article will take you through the common SQL errors that you might encounter while working with sql, sql-server,  tsql. The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue. SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem.

SQL Problem :

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?

Solution :

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 
    SELECT NULL As Test 

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

    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' 

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


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;



Create a new schema with the table TableName

CREATE TABLE SchemaName.TableName (Col int);


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

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



You can see a live demo on Rextester.

Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code.

Leave a Reply

Your email address will not be published. Required fields are marked *