Notes
L’accès à cette page nécessite une autorisation. Vous pouvez essayer de vous connecter ou de modifier des répertoires.
L’accès à cette page nécessite une autorisation. Vous pouvez essayer de modifier des répertoires.
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