/* 
	RemoveExistingStoredProcsAndTriggers.sql

	for the Control System database schema
*/

use control

DECLARE @CONTROLDB varchar(30)
SET @CONTROLDB = db_name()
PRINT 'Using ' + db_name()

IF (LEFT(@CONTROLDB,7)<>'control' AND @CONTROLDB<>'Defect_Test')
BEGIN
	PRINT 'ERROR, active DB is ' + @CONTROLDB + ' (should be Control or equivalent, or Defect_Test)'
	DECLARE @errmsg0 varchar(128)
	SET @errmsg0='Aborting, active DB is ' + @CONTROLDB
	raiserror(@errmsg0, 20, 1) with log
end

/*
 Remove our existing stored procedures 

 (note: the "grant EXEC permissions on our existing stored procedures" section
 near bottom of ControlProcedures.sql is almost identical code, so if something has to
 change here, see if it needs changing there too.)
*/

declare @procName sysname
declare ourCursor cursor
FOR
SELECT name FROM sysobjects WHERE OBJECTPROPERTY(id, N'IsProcedure') = 1 AND Name LIKE 'NV_%'
open ourCursor
FETCH NEXT FROM ourCursor into @procName
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT 'Dropping procedure ' + @procName 
	EXEC('drop procedure ' + @procName)
	FETCH NEXT FROM ourCursor into @procName
END
close ourCursor
deallocate ourCursor

/* --------------------------------------------------------- 
 Remove our existing triggers 
*/

declare @triggerName sysname
declare ourCursor cursor
FOR
SELECT name FROM sysobjects WHERE OBJECTPROPERTY(id, N'IsTrigger') = 1 AND name LIKE 'NV_%'
open ourCursor
FETCH NEXT FROM ourCursor into @triggerName
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT 'Dropping trigger ' + @triggerName 
	EXEC('drop trigger ' + @triggerName)
	FETCH NEXT FROM ourCursor into @triggerName
END
close ourCursor
deallocate ourCursor
go

