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