by Oz Evren, Software Design Engineer for Microsoft code name "Oslo"

 

One feature that is commonly requested for the “M” command-line utility, mx.exe, is being able to uninstall images. This is very useful when you’re developing a set of models and want to make sure you’ve cleaned up prior installations in the repository before the next test iteration. Or perhaps you’re just following through a sample but screwed something up.

 

In the May CTP bits, we started working on parts of uninstall support for schema created by an image, but didn’t expose it as a user-visible feature as we will in future CTPs. So if you want to get your hands on this functionality right now, give this SQL script a try (below and attached). This creates a stored procedure creatively named “Uninstall,” which takes one argument, the id of the image to uninstall which can be obtained from the [Catalog.Runtime].[Images] table.

 

Enjoy.J

 

Note: this script is corrected from an earlier version.

 

create procedure [Catalog.Runtime].[Uninstall]

      @image int

as

begin

 

declare @schemas as table ([schema_id] int not null primary key);

 

with [AllModuleNames](ModuleName) as (

      select M.Module from [Catalog.Runtime].[ImageModules] as M

      where M.[Image] = @image

)

insert into @schemas

select S.[schema_id] from sys.schemas as S

inner join [AllModuleNames] as M

on M.[ModuleName] collate Latin1_General_100_CS_AS_KS_WS = S.[name] collate Latin1_General_100_CS_AS_KS_WS or

   '$MRuntime.'+M.[ModuleName] collate Latin1_General_100_CS_AS_KS_WS = S.[name] collate Latin1_General_100_CS_AS_KS_WS

  

declare @items as table ([ordinal] int not null, [type] sysname not null, [object_id] int null, [schema_id] int not null, [constraint_name] sysname null);

 

insert into @items select 1, N'trigger', O.object_id, O.schema_id, null from sys.triggers as T

    inner join sys.objects as O on O.object_id = T.object_id

    where O.schema_id in (select [schema_id] from @schemas);

 

insert into @items select 2, N'procedure', O.object_id, O.schema_id, null from sys.objects as O

    where O.type = 'P' and O.schema_id in (select [schema_id] from @schemas);

 

with [Constraints]([name], [schema_id], [object_id], [parent_object_id]) as (

    select F.name, F.schema_id, F.object_id, F.parent_object_id from sys.foreign_keys as F

    union all

    select D.name, D.schema_id, D.object_id, D.parent_object_id from sys.default_constraints as D

    union all

    select C.name, C.schema_id, C.object_id, C.parent_object_id from sys.check_constraints as C

)

insert into @items select 3, N'constraint', C.[parent_object_id], C.[schema_id], C.[name] from Constraints as C

    inner join sys.tables as T on C.parent_object_id = T.object_id

    where T.schema_id in (select [schema_id] from @schemas);

 

insert into @items select 4, N'view', V.object_id, V.schema_id, null from sys.views as V

    where V.schema_id in (select [schema_id] from @schemas);

 

insert into @items select 5, N'function', O.object_id, O.schema_id, null from sys.objects as O

    where (O.type = 'FN' or O.type = 'TF' or O.type = 'IF') and O.schema_id in (select [schema_id] from @schemas);

 

insert into @items select 6, N'table', T.object_id, T.schema_id, null from sys.tables as T

    where T.schema_id in (select [schema_id] from @schemas);

     

insert into @items select 7, N'type', T.user_type_id, T.schema_id, null from sys.types as T

    where T.schema_id in (select [schema_id] from @schemas);

     

insert into @items select 8, N'schema', null, S.[schema_id], null from @schemas as S;

 

 

declare @script nvarchar(max) = N'

set xact_abort on;

begin transaction;

';

 

declare @type sysname;

declare @item_schema_id int;

declare @item_object_id int;

declare @constraint_name sysname;

 

declare itemCursor cursor local fast_forward for

    select [type], [schema_id], [object_id], [constraint_name] from @items order by [ordinal] asc;

open itemCursor;

fetch itemCursor into @type, @item_schema_id, @item_object_id, @constraint_name;

while @@fetch_status = 0

begin

    declare @item_name nvarchar(max) = quotename(schema_name(@item_schema_id));

    if (@item_object_id is not null)

    begin

        if (@type = N'type')

        begin

            set @item_name += N'.' + quotename(type_name(@item_object_id));

        end

        else

        begin

            set @item_name += N'.' + quotename(object_name(@item_object_id));

        end

    end

 

    if (@constraint_name is not null)

    begin

        set @script += N'alter table ' + @item_name + N' drop ' + @type + N' ' + quotename(@constraint_name) + N';';

    end

    else

    begin

        set @script += N'drop ' + @type + N' ' + @item_name + N';';

    end

 

    fetch itemCursor into @type, @item_schema_id, @item_object_id, @constraint_name;

end

close itemCursor;

deallocate itemCursor;

 

set @script += N'

delete from [Catalog.Runtime].[ImageResources] where [Image] = ' + convert(nvarchar,@image) + N';

delete from [Catalog.Runtime].[ImageModules] where [Image] = ' + convert(nvarchar,@image) + N';

delete from [Catalog.Runtime].[ImageDependencies] where [DependentImage] = ' + convert(nvarchar,@image) + N';

delete from [Catalog.Runtime].[Images] where [Id] = ' + convert(nvarchar,@image) + N';';

 

 

set @script += N'commit transaction;';

 

begin try

 

      exec(@script);

 

end try

begin catch

    declare @ErrorMessage nvarchar(max);

    declare @ErrorSeverity int;

    declare @ErrorState int;

    select

        @ErrorMessage = error_message(),

        @ErrorSeverity = error_severity(),

        @ErrorState = error_state();

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

end catch

 

end