微软sql服务器可以卸载,删除具有所有依赖性的表（Microsoft SQL Server）

create procedure [dbo].[spDropObject] (@fullname nvarchar(520))

as

begin

begin try

declare @type nvarchar(5)

declare @resolvedFullname nvarchar(520)

declare @resolvedName nvarchar(255)

set @type = null

set @resolvedFullname = null

set @resolvedName = null

--find the object

select

@type = o.[type]

,@resolvedFullname = \'[\' + object_schema_name(o.id) + \'].[\' + o.[name] + \']\'

,@resolvedName = \'[\' + o.[name] + \']\'

from dbo.sysobjects o

where id = object_id(@fullname)

--PROCEDURE

if(@type = \'P\')

begin

exec(\'drop procedure \' + @resolvedFullname);

return;

end

--VIEW

if(@type = \'V\')

begin

exec(\'drop view \' + @resolvedFullname);

return;

end

--FUNCTION

if(@type = \'FN\' or @type = \'TF\')

begin

exec(\'drop function \' + @resolvedFullname);

return;

end

--TRIGGER

if(@type = \'TF\')

begin

exec(\'drop trigger \' + @resolvedFullname);

return;

end

--CONSTRAINT

if(@type = \'C\' or @type = \'UQ\' or @type = \'D\' or @type = \'F\' or @type = \'PK\' or @type = \'K\')

begin

declare @fullTablename nvarchar(520);

set @fullTablename = null

--find the contraint\'s table

select @fullTablename =\'[\' + object_schema_name(t.[object_id]) + \'].[\' + t.[Name] + \']\'

from sys.tables t

join sys.schemas s on t.schema_id = s.schema_id

where t.object_id = (select parent_obj from dbo.sysobjects where id = object_id(@resolvedFullname))

exec(\'alter table \' + @fullTablename + \' drop constraint \' + @resolvedName);

return;

end

--TABLE (drop all constraints then drop the table)

if(@type = \'U\')

begin

--find FK references to the table

declare @fktab table([Name] nvarchar(255))

insert @fktab

select

[Name] = \'[\' + object_name(fkc.[constraint_object_id]) + \']\'

/*

,[Parent] = \'[\' + object_schema_name(fkc.[parent_object_id]) + \'].[\' + object_name(fkc.[parent_object_id]) + \']\'

,[Ref] = \'[\' + object_schema_name(fkc.[referenced_object_id]) + \'].[\' + object_name(fkc.[referenced_object_id]) + \']\'

*/

from sys.foreign_key_columns as fkc

where referenced_object_id = object_id(@resolvedFullname)

order by [Name]

--iterate FKs

while(1=1)

begin

declare @constraint nvarchar(255)

set @constraint = null

select top 1

@constraint = [Name]

from @fktab

if(@constraint is not null)

begin

--drop FK constraint

exec [dbo].[spDropObject] @constraint;

delete from @fktab where [Name] = @constraint --remove current record from working table

end

else break;

end

--find constraints for table

declare @constraintTab table ([Name] nvarchar(255));

insert @constraintTab

select [name]

from sys.objects

where parent_object_id = object_id(@resolvedFullname)

order by [name]

--iterate constraints

while(1=1)

begin

set @constraint = null;

select top 1 @constraint = [Name] from @constraintTab

if(@constraint is not null)

begin

--drop constraint

exec [dbo].[spDropObject] @constraint;

delete from @constraintTab where [Name] = @constraint --remove current record from working table

end

else break;

end

--drop table

exec(\'drop table \' + @resolvedFullname);

return;

end

end try

begin catch

declare @message nvarchar(max)

set @message = error_message( ) ;

print @message

end catch

end