SQL Server view - bad naming convention?

Question

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?

Answer 1

Use quotename():

Set @SQL = 'SELECT TOP 10 * FROM ' + QUOTENAME(@TableName);

Answer 2

I truly detest cursors and there is no need for a cursor here at all. You can greatly simplify this code in a couple of ways. First I am using sys.views instead of the INFORMATION_SCHEMA views. And then I am using sql to build a dynamic sql string instead of a cursor. Look how simple this can be.

declare @SQL nvarchar(max) = '';

select @SQL = @SQL + 'select top 10 * from ' + QUOTENAME(v.name) + '; select ''('' + convert(varchar(2), @@ROWCOUNT) + '' rows affected'';'
from sys.views v
where v.name LIKE 'V_WFC%'

print @SQL
exec sp_executesql @SQL