SQL Server view – bad naming convention?

  • A+
Category:Languages
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?


Use quotename():

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

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: