question

Hi Everyone,

I am getting an error message with exec command. Below is the query:

                    
drop table #schema                    
                    
select schema_name into #schema from information_schema.schemata                     
    where schema_name not in ('dbo','guest','INFORMATION_SCHEMA','sys')                    
                    
DECLARE @string sysname, @schemaname sysname                    
                    
DECLARE string_cursor CURSOR for select schema_name from #schema;                    
                    
open string_cursor;                    
                    
FETCH NEXT FROM string_cursor INTO @schemaname;                    
                    
WHILE (@@FETCH_STATUS <> -1)                    
                    
BEGIN;                    
                    
set @string='drop schema ['+@schemaname+']'                    
                    
exec (@string)                    
                    
FETCH NEXT FROM string_cursor INTO @schemaname;                    
                    
end;                    
                    
CLOSE string_cursor;                    
                    
DEALLOCATE string_cursor;                    

Exec is failing on the first row and does not read other rows in the table. It should continue reading all the rows until it read entire table. Even if there are error on any row, it should not abort the transaction. Below is the error message:

Msg 3729, Level 16, State 1, Line 1

Cannot drop schema 'appdba' because it is being referenced by object 'BACKUP_TITAN2'.