How to use "USE ?" with CURSOR?-CodePudding

I found a great example about how to use a cursor:

DECLARE @DB_Name varchar(100) 
DECLARE @Command nvarchar(200) 

DECLARE database_cursor CURSOR FOR 
    SELECT name 
    FROM MASTER.sys.sysdatabases 

OPEN database_cursor 

FETCH NEXT FROM database_cursor INTO @DB_Name 

WHILE @@FETCH_STATUS = 0 
BEGIN 
     SELECT @Command = 'SELECT '   ''''   @DB_Name   ''''   ', SF.filename, SF.size FROM sys.sysfiles SF'
     EXEC sp_executesql @Command 

     FETCH NEXT FROM database_cursor INTO @DB_Name 
END 

CLOSE database_cursor 
DEALLOCATE database_cursor

The problem is that when I execute it it always returns the same filename and size. The article explains that it's because we have removed USE ?.

So let's put it back:

DECLARE @DB_Name varchar(100) 
DECLARE @Command nvarchar(200) 

DECLARE database_cursor CURSOR FOR 
    SELECT name 
    FROM MASTER.sys.sysdatabases 

OPEN database_cursor 

FETCH NEXT FROM database_cursor INTO @DB_Name 

WHILE @@FETCH_STATUS = 0 
BEGIN 
     SELECT @Command = ' USE ? SELECT '   ''''   @DB_Name   ''''   ', SF.filename, SF.size FROM sys.sysfiles SF'
     EXEC sp_executesql @Command 

     FETCH NEXT FROM database_cursor INTO @DB_Name 
END 

CLOSE database_cursor 
DEALLOCATE database_cursor

But SQL Server throws these errors:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '?'

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '?'

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '?'

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '?'

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '?'

Exactly 5 times which is the number of databases I have.

How to use this cursor?

CodePudding user response：

You need to use the @DB_Name variable instead of a ?:

DECLARE @DB_Name varchar(100) 
DECLARE @Command nvarchar(200) 
DECLARE database_cursor CURSOR FOR 
SELECT name 
FROM MASTER.sys.sysdatabases 

OPEN database_cursor 

FETCH NEXT FROM database_cursor INTO @DB_Name 

WHILE @@FETCH_STATUS = 0 
BEGIN 
     SELECT @Command = ' USE '   @DB_Name   ' SELECT '   ''''   @DB_Name   ''''   ', SF.filename, SF.size FROM sys.sysfiles SF'
     EXEC sp_executesql @Command 

     FETCH NEXT FROM database_cursor INTO @DB_Name 
END 

CLOSE database_cursor 
DEALLOCATE database_cursor

CodePudding user response：

The chosen answer works, but it does not protect you from SQL injection. If someone has the ability to create a database on this system, they could - for example - perhaps under the guise of testing, QA, or through a UI that lets users create databases - create a database named tempdb; SELECT name,salary FROM Accounting.dbo.Employees; and gain access to sensitive information (or, worse, do damage!) in a database they don't currently have access to, and this path is only made possible by the dynamic SQL's execution context.

Also you should not be using deprecated compatibility views like sysdatabases and sysfiles, and you should protect yourself from offline and otherwise inaccessible databases which will cause your process to grind to a halt. A couple of other minor things like your strings need to be longer (to account for potentially longer database names), should always be nvarchar because people can name their database