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