SQL Script to Uninstall an "M" Image


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

ImageUninstall.sql

Comments (0)