-- Replace @Scope value with the desired scope.  Default is 3 (User)
-- Cursor section has a commented out line that can be uncommented and used to specify a single form or rewritten to allow for pattern matching

-- Script Date 12-4-2015 
-- Tony Trus
-- Onepax LLC


BEGIN TRAN

SET NOCOUNT ON
DECLARE 
 @FormID INT
,@FormName nvarchar(50)
,@Scope INT

set @Scope = 3 --scope 1 for SITE, scope 2 for GROUP, scope 3 for USER
if @Scope < 1 or @Scope > 3
  RAISERROR('SCOPE MUST BE 1, 2 or 3',20,1) with LOG

DECLARE FormDelCur cursor local static for
SELECT Forms.ID, Forms.Name FROM Forms WHERE 
  [ScopeType] = @Scope
  --AND [Name] = N'JobOrders' 

open FormDelCur
fetch next from FormDelCur into @FormID, @FormName

while @@FETCH_STATUS = 0
BEGIN TRY
   DELETE FROM Forms WHERE ID = @FormID
   DELETE FROM FormEventHandlers WHERE FormID = @FormID
   DELETE FROM FormComponents WHERE FormID = @FormID
   DELETE FROM ActiveXComponentProperties WHERE FormID = @FormID
   DELETE FROM Variables WHERE FormID = @FormID
   DELETE FROM FormComponentDragDropEvents WHERE FormID = @FormID
   DELETE FROM DerivedFormOverrides WHERE FormID = @FormID
   DELETE FROM ActiveXScripts WHERE [Name] = @FormName AND [ScopeType] = @Scope
   DELETE FROM ActiveXScriptLines WHERE [ScriptName] = @FormName AND [ScopeType] = @Scope
   print 'Successfully deleted form ' + @FormName + ' with ' + CASE when @Scope = 1 then 'SITE SCOPE'
                                                                    when @Scope = 2 then 'GROUP SCOPE'
								    when @Scope = 3 then 'USER SCOPE'
							       END
   fetch next from FormDelCur into @FormID, @FormName
END TRY
BEGIN CATCH
   print 'Unable to delete form ' + @FormName + 'with ' + CASE when @Scope = 1 then 'SITE SCOPE'
                                                               when @Scope = 2 then 'GROUP SCOPE'
							       when @Scope = 3 then 'USER SCOPE'
							  END
   fetch next from FormDelCur into @FormID, @FormName
END CATCH
close FormDelCur
Deallocate FormDelCur
ROLLBACK TRAN
--COMMIT TRAN