TSQL script returning wrong result

Question

I'm trying to find out if a particular index name "Test" exists in all databases using below code. The result I'm getting is always "NOT EXIST" for all databases in an instance. However, the index does indeed exist on few of the databases. Can someone please point out where the code is wrong?

DECLARE @Name AS VARCHAR(100)
DECLARE dbCur CURSOR FOR
SELECT name FROM SYS.databases where name not in ('master','msdb','model','tempdb')
OPEN dbCur
FETCH NEXT FROM dbCur
INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
--Check if index exist    
   EXECUTE('USE [' + @Name + ']')
   If 1 = ((SELECT COUNT(*) as index_count
        FROM sys.indexes 
        WHERE object_id = OBJECT_ID('dbo.TestTable')
        AND name = 'Test')) 
    Print 'INDEX EXIST ' + @Name;
    ELSE
    Print 'INDEX DOES NOT EXIST ' + @Name;
   FETCH NEXT FROM dbCur
INTO @Name
END
CLOSE dbCur
DEALLOCATE dbCur

Result:

  INDEX DOES NOT EXIST DB1
  INDEX DOES NOT EXIST DB2
  INDEX DOES NOT EXIST DB3
  INDEX DOES NOT EXIST DB4

Answer 1

Your problem might be related to issuing a USE inside of a cursor. Instead, I would suggest using the following approach, so you can get rid of the cursor.

EXEC sp_MSforeachdb 
'USE ? 
 IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
 BEGIN
      IF EXISTS(SELECT 1 FROM sys.indexes ind WHERE object_id = OBJECT_ID(''dbo.TestTable'') AND name = ''Test'') 
    PRINT ''Exists in '' + DB_NAME()  
ELSE 
    PRINT ''Not Exists in '' + DB_NAME()
END'

Please note that technically, sp_MSforeachdb is an undocumented feature but it simply works.

Answer 2

When USE appears in a nested batch, it affects only statements in that batch. The database context is restored after the batch completes. EG

use tempdb
exec ('use master; select db_name()')
select db_name()

outputs

master
tempdb