USE [NA]

/*Version 36.2014.03.05*/

/*
-2014.03.05:		drop re-triggers
*/

declare @query varchar(max)

if not exists (select * from sysobjects where [name] like 'FmtSetup')
	set @query = 'CREATE FUNCTION [dbo].[FmtSetup]'
else
	set @query = 'ALTER FUNCTION [dbo].[FmtSetup]'
	
set @query = @query + '
(
	@table varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
	declare @view varchar(max), @fmt varchar(max), @idx int, @idx2 int, @server varchar(max), @redb varchar(max), @company varchar(max), @parameters varchar(max)

	if @table is null
		select @view = object_definition(object_id(''All$Contact''))
	else
		select @view = object_definition(object_id(@table))

	--print @view

	select @idx = charindex(''from'', @view) + 4

	--print @idx

	select @fmt = substring(@view, @idx, 500)

	--print @fmt

	select @idx = charindex(''['', @fmt) + 1

	select @fmt = substring(@fmt, @idx, 500)

	--print @fmt

	select @idx = charindex('']'', @fmt)

	select @server = substring(@fmt, 0, @idx)

	--print @server

	select @fmt = substring(@fmt, @idx+1, 500)

	--print @fmt

	select @idx = charindex(''['', @fmt) + 1

	select @fmt = substring(@fmt, @idx, 500)

	--print @fmt

	select @idx = charindex('']'', @fmt)

	select @redb = substring(@fmt, 0, @idx)

	--print @redb

	select @fmt = substring(@fmt, @idx+1+len(''.[dbo].[''), 500)

	--print @fmt

	select @idx = charindex(''$'', @fmt)

	select @company = substring(@fmt, 0, @idx)

	--print @company

	select @parameters = '''''''' + @server + '''''', '''''' + @redb + '''''', '''''' + @company + ''''''''

	--print @parameters

	--sp_helptext ''All$Contact''

	return @parameters
END'
exec(@query)

declare @setup varchar(max), @fmt varchar(max), @idx int, @redb varchar(max), @ridb varchar(max)
select @setup = dbo.FmtSetup(null)
select @idx = charindex(', ''', @setup) + 3
select @fmt = substring(@setup, @idx, 500)
select @idx = charindex('''', @fmt)
select @redb = substring(@fmt, 0, @idx)
select @ridb = DB_NAME()

--drop views and procs
print 'drop views and procs'
declare @name varchar(max)
declare view_cur cursor for
select [name] from sys.views where not name in ('anaSubscriptions')
open view_cur
fetch next from view_cur into @name
while @@fetch_status = 0
begin
	set @query = 'drop view [' + RTRIM(@name) + ']'
	exec(@query)
	fetch next from view_cur into @name
end
close view_cur
deallocate view_cur

declare proc_cur cursor for
select name from sys.procedures where not name in ('sp_create', 'sp_update', 'sp_create_pln', 'sp_create_lts', 'sp_create_indexes', 'sp_JobRunState', 'sp_start_job_and_wait', 'sp_create_mw', 'sp_create_mw_dim', 'sp_create_mw_indexes') and name not like 'sp_repl%' and name not like 'sav_%' and name not like 'sp_create%'
open proc_cur
fetch next from proc_cur into @name
while @@fetch_status = 0
begin
	set @query = 'drop proc [' + RTRIM(@name) + ']'
	exec(@query)
	fetch next from proc_cur into @name
end
close proc_cur
deallocate proc_cur

--drop triggers in RE
set @query = '
use [' + @redb + ']
print ''drop re-triggers''
declare @trigger varchar(max), @query varchar(max)
declare retr cursor for
select
	--[name]
	--object_name([parent_obj]) as [Table]
	[name] as [Trigger]
from
	sysobjects
where
	[type] = ''TR''
	and ([Name] like ''%tr_aggregation%'' or [Name] like ''%tr_ri_%'')
	and not [Name] like ''%objecttracking''
	and not [Name] like ''%$tr_ri_ml''
	and not [Name] like ''tr_ri_company''

open retr
fetch next from retr into @trigger
while @@fetch_status = 0
begin
	set @query = ''drop trigger ['' + rtrim(@trigger) + '']''
	exec(@query)
	fetch next from retr into @trigger
end 
close retr
deallocate retr'
exec(@query)


--declare @query varchar(max)
select @query = 'sp_update ' + @setup
print @query
exec(@query)
