Fetching Database Name with Space

Thanks for looking into my post. I am trying to run a query across all the database in SQL Server but the cursor is not fetching if the database contains space and the database is online.

Code

DECLARE @tsql nvarchar(max)
DECLARE @dbname nvarchar(max)

DECLARE MyCur CURSOR STATIC FORWARD_ONLY FOR
SELECT [name] 
FROM sys.databases 
 -- WHERE [name] NOT IN ('tempdb','Managed Metadata Service_9393')
--and state_desc='ONLINE' 

OPEN MyCur 

WHILE (1=1)   
BEGIN   
FETCH NEXT FROM MyCur INTO @dbname

IF @@FETCH_STATUS <> 0 
    BREAK 

SET @tsql = 'use ' + @dbname + ' select @@servername'
EXEC sp_executesql @tsql 
END   

CLOSE MyCur;   
DEALLOCATE MyCur;

​x
 
DECLARE @tsql nvarchar(max)DECLARE @dbname nvarchar(max)​DECLARE MyCur CURSOR STATIC FORWARD_ONLY FORSELECT [name] FROM sys.databases  -- WHERE [name] NOT IN ('tempdb','Managed Metadata Service_9393')--and state_desc='ONLINE' ​OPEN MyCur ​WHILE (1=1)   BEGIN   FETCH NEXT FROM MyCur INTO @dbname​IF @@FETCH_STATUS <> 0     BREAK ​SET @tsql = 'use ' + @dbname + ' select @@servername'EXEC sp_executesql @tsql END   ​CLOSE MyCur;   DEALLOCATE MyCur;    ​

Error

 Msg 911, Level 16, State 1, Line 1
 Database 'Managed' does not exist. Make sure that the name is entered correctly.

 
 Msg 911, Level 16, State 1, Line 1 Database 'Managed' does not exist. Make sure that the name is entered correctly.​

Answer

Delimit Identify your object’s name. In normal terms that would be wrapping it with brackets ([]), however, as this appears to be dynamic SQL you should be using QUOTENAME:

SET @tsql = N'USE ' + QUOTENAME(@dbname) + N'; SELECT @@servername;';

 
SET @tsql = N'USE ' + QUOTENAME(@dbname) + N'; SELECT @@servername;';​

But, ideally, don’t use object names that require you to delimit identify your objects.

Advertisement

Answer