DECLARE @TableName AS VARCHAR(250); DECLARE @SQL AS VARCHAR(500); DECLARE @ViewCheck as CURSOR; SET @ViewCheck = CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'VIEW' AND TABLE_NAME LIKE 'V_WFC%' OPEN @ViewCheck FETCH NEXT FROM @ViewCheck INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN Set @SQL = 'SELECT TOP 10 * FROM ' + @TableName PRINT(@SQL) EXEC(@SQL) FETCH NEXT FROM @ViewCheck INTO @TableName; END CLOSE @ViewCheck
I have a cursor that runs through all SQL views in a particular schema to sanity check that they continue to function, some are tied to reporting and some used as an application data source in ProSolution.
One of these views is named UnmarkedRegister(Today) the brackets used to differentiate it from a from a similar view, this one is used within an application to drive display data.
While the query runs as expected, returning the correct data - the cursor returns an error
Msg 208, Level 16, State 1, Line 1
Invalid object name 'V_WFC_UnmarkedRegister'
and I'm wondering why the bracketed section is omitted in the EXEC(SQL) section of the cursor?
Set @SQL = 'SELECT TOP 10 * FROM ' + QUOTENAME(@TableName);