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