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