|
1 |
originally posted 2013-6-27 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 |
--Delete SQL object using its schema-qualified name. For tables, the constraints are dropped first. 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 = 'TR') 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 |