Turn SQL tables into Views - with Dynamic SQL

Question

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

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

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