USP_CLEANSPEC

create proc dbo.[USP_CLEANSPEC] (
    @specType as varchar(max)
    ,@name as varchar(max)
    ,@specId as uniqueidentifier 
    )
as
begin
    declare @procName as nvarchar(max); -- used to drop procedure/ufns

    declare @specIdString as nvarchar(max);
    set @specIdString = cast(@specId as nvarchar(max));

    declare @CHANGEAGENTID as uniqueidentifier;    
    exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

    -- Prepare context information for the delete operations

    --Cache current context information 

    declare @CONTEXTCACHE varbinary(128); 
    set @CONTEXTCACHE = CONTEXT_INFO(); 

    --Set CONTEXT_INFO to @CHANGEAGENTID 

    set CONTEXT_INFO @CHANGEAGENTID; 

    if @specType='table' 
    begin
        if exists(select * from dbo.[TABLECATALOG] where TABLENAME = @name and ID <> @specId) 
        begin
            -- The error raised is less than 11 so it does not stop revisions from running

            -- A customization that overwrites our dead table name should be safe this way.

            raiserror ('A TABLECATALOG entry exists for "%s" but has a different id. No action performed.', 6, 1, @name);
            return;
        end;

        -- Drop the table itself.

        if exists(select * from INFORMATION_SCHEMA.TABLES where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = (@name + 'AUDIT'))
            exec ('drop table dbo.[' + @name + ']');

        -- Drop audit table

        if exists(select * from INFORMATION_SCHEMA.TABLES where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = (@name + 'AUDIT'))
            exec ('drop table dbo.[' + @name + 'AUDIT]');

         --Drop any UFN's or USP's that were created by the tablespec or codetablespec...

         declare @SQL as nvarchar(max);
         set @SQL = replace((
            select 
                'drop ' + lower([ROUTINE_TYPE]) + ' dbo.[' + [ROUTINE_NAME] + '];'
            from 
                INFORMATION_SCHEMA.ROUTINES
            where 
                not exists(select * from INFORMATION_SCHEMA.TABLES where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @name)
                and [ROUTINE_SCHEMA] = 'dbo'
                and (
                    [ROUTINE_NAME] like replace('UFN_' + @name + '_%_GETID', '_', '\_') escape '\'
                    or [ROUTINE_NAME] like replace('UFN_' + @name + '_%_GETDESCRIPTION', '_', '\_') escape '\'
                    or [ROUTINE_NAME] like replace('USP_' + @name + '_%_GETLIST', '_', '\_') escape '\'
                    or [ROUTINE_NAME] like replace('USP_' + @name + '_DELETEBYID_WITHCHANGEAGENTID', '_', '\_') escape '\'
                    or [ROUTINE_NAME] like replace('USP_' + @name + 'AUDIT_SETDELETEDCHANGEAGENTID', '_', '\_') escape '\'
                    or [ROUTINE_NAME] like replace('UFN_' + @name + '_GETID', '_', '\_') escape '\'
                    or [ROUTINE_NAME] like replace('UFN_' + @name + '_GETDESCRIPTION', '_', '\_') escape '\'
                    or [ROUTINE_NAME] like replace('USP_' + @name + '_CREATEENTRY', '_', '\_') escape '\'
                    or [ROUTINE_NAME] like replace('USP_' + @name + '_DELETEENTRY', '_', '\_') escape '\'
                    or [ROUTINE_NAME] like replace('USP_' + @name + '_GETENTRYDATA', '_', '\_') escape '\'
                    or [ROUTINE_NAME] like replace('USP_' + @name + '_GETLIST', '_', '\_') escape '\'
                    or [ROUTINE_NAME] like replace('USP_' + @name + '_NORMALIZESEQUENCENUMBERS', '_', '\_') escape '\'
                    or [ROUTINE_NAME] like replace('USP_' + @name + '_UPDATEENTRY', '_', '\_') escape '\'  
                )
            for xml path('')), ';', ';' + char(13))
        ;

        begin try
            if len(isnull(@SQL,'')) > 0    
                exec (@SQL);
        end try
        begin catch
            raiserror ('Error dropping related UFN and USP for table %s', 6, 1, @name);
        end catch;

        -- Drop the record type for table

        if (select COUNT(*) from dbo.RECORDTYPE where BASETABLENAME = @name) > 1
            raiserror ('Cannot delete RECORTYPE entry for %s, there are more than one entries', 6, 1, @name);
        else
        begin            
            delete from dbo.RECORDTYPE where BASETABLENAME = @name
            ;
        end

        -- Finally delete the entry from TABLECATALOG

        delete from dbo.TABLECATALOG where ID = @specId
        ;

    end
    else if @specType='dataform'
    begin
        if not exists( select * from dbo.DATAFORMTEMPLATECATALOG as DFTC where ID = @specId and DFTC.TEMPLATENAME = @name) 
        begin            
            raiserror ('Cannot find form ''%s'' with an id of ''%s''', 6, 1,@name, @specIdString );
            return
        end        

        -- remove the ADD procedure for the template

        select @procName = SAVEPROCEDURE from dbo.DATAFORMTEMPLATECATALOG where ID = @specId;
        if len(coalesce(@procName,''))>0
             exec ('drop proc ' + @procName);
        select @procName = LOADPROCEDURE from dbo.DATAFORMTEMPLATECATALOG where ID = @specId;
        if len(coalesce(@procName,''))>0
             exec ('drop proc ' + @procName);

        declare addNewNames cursor for 
            select 
                p.name 
            from 
                DATAFORMINSTANCECATALOG as dtc
            join 
                sys.procedures as p 
                    on (
                        'USP_DATAFORM_ADDNEW_' + lower(replace(cast(ID as nvarchar(max)),'-','_')) = p.name
                        or 'USP_DATAFORM_LOAD_' + lower(replace(cast(ID as nvarchar(max)),'-','_')) = p.name
                        or 'USP_DATAFORM_UPDATE_' + lower(replace(cast(ID as nvarchar(max)),'-','_')) = p.name )
            where 
                dtc.DATAFORMTEMPLATECATALOGID = @specId
            ;

        open addNewNames;
        fetch next from addNewNames into @procName
        while @@FETCH_STATUS = 0
        begin
            exec ('drop proc ' + @procName);
            fetch next from addNewNames into @procName;
        end;
        close addNewNames;
        deallocate addNewNames;

        -- Delete instantiated version of this dataform

        delete from dbo.DATAFORMINSTANCECATALOG where DATAFORMTEMPLATECATALOGID = @specId;

        -- Delete the template

        delete from dbo.DATAFORMTEMPLATECATALOG where ID = @specId;
    end
    else if @specType='datalist' 
    begin
        select @procName = PROCEDURENAME from dbo.DATALISTCATALOG
            where ID = @specId and NAME = @name;
        if len(COALESCE(@procName,''))>0 
        begin 
            begin try
                exec ('drop proc ' + @procName);
            end try
            begin catch
                raiserror ('Cannot drop procedure ''%s''', 6, 1,@procName );
            end catch
        end
        else
            raiserror ('Cannot find datalist ''%s'' with an id of ''%s''', 6, 1,@name, @specIdString );

        delete from dbo.DATALISTCATALOG 
            where ID = @specId and NAME = @name;
    end
    else if @specType='searchlist'
    begin
        select @procName = PROCEDURENAME from dbo.SEARCHLISTCATALOG
            where ID = @specId and NAME = @name;
        if len(COALESCE(@procName,''))>0 
        begin
            begin try
                exec ('drop proc ' + @procName);
            end try
            begin catch
                raiserror ('Cannot drop procedure ''%s''', 6, 1,@procName );
            end catch
        end
        else
            raiserror ('Cannot find searchlist ''%s'' with an id of ''%s''', 6, 1,@name, @specIdString );

        delete from dbo.SEARCHLISTCATALOG 
            where ID = @specId and NAME = @name;
    end
    else if @specType='recordoperation'
    begin    
        select @procName = PROCEDURENAME from dbo.RECORDOPERATIONCATALOG
            where ID = @specId and DISPLAYNAME = @name;
        if len(COALESCE(@procName,''))>0 
        begin
            begin try        
                exec ('drop proc ' + @procName);    
            end try
            begin catch
                raiserror ('Cannot drop procedure ''%s''', 6, 1,@procName );
            end catch
        end
        else
            raiserror ('Cannot find recordoperation ''%s'' with an id of ''%s''', 6, 1,@name, @specIdString );

        delete from dbo.RECORDOPERATIONCATALOG 
            where ID = @specId and DISPLAYNAME = @name;        
    end
    else if @specType='translationfunction'
    begin    
        select @procName = FUNCTIONNAME from dbo.TRANSLATIONFUNCTIONCATALOG
            where ID = @specId and NAME = @name;
        if len(COALESCE(@procName,''))>0 
        begin
            begin try
                exec ('drop function ' + @procName);    
            end try
            begin catch
                raiserror ('Cannot drop function ''%s''', 6, 1,@procName );
            end catch;
        end
        else
            raiserror ('Cannot find translationfunction ''%s'' with an id of ''%s''', 6, 1,@name, @specIdString );

        delete from dbo.TRANSLATIONFUNCTIONCATALOG 
            where ID = @specId and NAME = @name
        ;
    end
    else if @specType='task'
    begin    
        select @procName = NAME from dbo.TASKCATALOG
            where ID = @specId and NAME = @name;
        if len(COALESCE(@procName,''))<=0 
            raiserror ('Cannot find task ''%s'' with an id of ''%s''', 6, 1,@name, @specIdString );

        delete from dbo.TASKCATALOG 
            where ID = @specId and NAME = @name
        ;
    end
    else 
    begin
        RAISERROR('Invalid specType specified, must be one of ''task'', ''table'', ''dataform'', ''datalist'', ''searchlist'', ''recordoperation'', ''translationfunction'''
                  ,16, 1 );                    
    end;

    --Reset CONTEXT_INFO to previous value 

    if not @CONTEXTCACHE is null 
    set CONTEXT_INFO @CONTEXTCACHE;
end