0

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
2

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.