Turn SQL tables into Views – with Dynamic SQL

All we need is an easy explanation of the problem, so here it is.

As the name would suggest, I need to turn a TON of sql tables into views, using dynamic SQL.

Motive-wise, it’s just a silly workaround to preserve a Legacy program.

So far I’m creating a temptable, iterating through and populating it w/table names, then Creating vws

Here’s the script so far, not working.
Error from SSMS: ‘Incorrect syntax near @viewName’ ln 17

[USE statement here]
GO

IF OBJECT_ID('tempdb..#tempTables') IS NOT NULL DROP TABLE #tempTables
GO

CREATE TABLE #tempTables (
    tableName nvarchar(128)
    )
GO

INSERT INTO #tempTables
    (tableName)
SELECT NAME FROM sys.tables WHERE TYPE = 'U'
ORDER BY NAME
GO

WHILE (SELECT count(*) FROM #tempTables) > 0

    BEGIN
        DECLARE @tableName nvarchar(128) = (SELECT TOP 1 tableName FROM #tempTables)
        DECLARE @viewName nvarchar(128) = 'vwAccess_' + @tableName
        DECLARE @sqlDrop nvarchar(MAX)

    --1) If @viewName exists drop @viewName
        IF EXISTS (SELECT * FROM sys.views WHERE NAME = @viewName)
        SELECT @sqlDrop = 'DROP VIEW dbo.' + @viewName
        EXEC sp_executesql @sqlDrop
        
    --2) Create @view as 
        EXECUTE('CREATE VIEW @viewName AS SELECT * from tableName')
        
    --3) Delete from tempTable 
        DELETE FROM #tempTables WHERE tableName = @tableName

    END

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Your specific error is due to you directly referencing your @viewName variable in your dynamic SQL string in Step 2, but it doesn’t exist in that context. You have to concatenate the value that variable holds to your dynamic SQL string instead. Something like this perhaps:

DECLARE @dynamicSQL NVARCHAR(MAX) = N'CREATE VIEW ' + QUOTENAME(@viewName) + ' AS SELECT * from tableName'

EXECUTE sp_ExecuteSQL @dynamicSQL

Note the usage of sp_ExecuteSQL in my example above, as this is the recommended way of executing dynamic SQL for security reasons. It looks like you’re already doing that in Step 1.

That being said, a little more context on your why would be important here. If you plan to remove, rename, or alter the underlying table that you’re currently creating views for, then your view will potentially no longer work as well, since a view is just an unmaterialized placeholder for a query that references other objects, and therefore depends on those objects. Please elaborate in your post on what the end goal of your tables are, and perhaps a better recommendation can be made to what you should do.

Method 2

This looks like a good use case for synonyms, rather than views.

SELECT * views can be fragile when the underlining schema changes, resulting in wonky workarounds, which are largely alleviated by just using a synonym.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply