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