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'.
You need to check if the scehma you are trying to drop has dependencies before attempting to drop it.
You might also be able to use a TRY / CATCH block to prevent any errors being terminal, but that isn't able to catch all errors, and I don't know off-hand if it can catch this one.
answered Dec 08 '09 at 08:28 AM