CODEWITHSUNDEEP
sqlsql-server
JonTout
JonTout

Reputation: 640

SQL Server view - bad naming convention?

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?

Upvotes: 12

Views: 2808

Answers (3)

Sean Lange
Sean Lange

Reputation: 33571

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

Upvotes: 5

Brad
Brad

Reputation: 3591

Because brackets usually identify a function call

changing this line:

Set @SQL = 'SELECT TOP 10 * FROM ' + @TableName

to this should fix it:

Set @SQL = 'SELECT TOP 10 * FROM [' + @TableName + ']'

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Use quotename():

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

Upvotes: 18

Related Questions