0

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
0

2 Answers 2

3

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.

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.