07-31-2023, 09:46 AM
One more sample
```
declare @objectId int, @objectName varchar(500), @schemaName varchar(500), @type nvarchar(30), @parentObjId int, @parentObjName nvarchar(500)
declare cur cursor
for
select obj.object_id, s.name as schema_name, obj.name, obj.type, parent_object_id
from sys.schemas s
inner join sys.sysusers u
on u.uid = s.principal_id
JOIN
sys.objects obj on obj.schema_id = s.schema_id
WHERE s.name = 'schema_name' and (type = 'p' or obj.type = 'v' or obj.type = 'u' or obj.type = 'f' or obj.type = 'fn')
order by obj.type
open cur
fetch next from cur into @objectId, @schemaName, @objectName, @type, @parentObjId
while @@fetch_status = 0
begin
if @type = 'p'
begin
exec('drop procedure ['+@schemaName +'].[' + @objectName + ']')
end
if @type = 'fn'
begin
exec('drop FUNCTION ['+@schemaName +'].[' + @objectName + ']')
end
if @type = 'f'
begin
set @parentObjName = (SELECT name from sys.objects WHERE object_id = @parentObjId)
exec('ALTER TABLE ['+@schemaName +'].[' + @parentObjName + ']' + 'DROP CONSTRAINT ' + @objectName)
end
if @type = 'u'
begin
exec('drop table ['+@schemaName +'].[' + @objectName + ']')
end
if @type = 'v'
begin
exec('drop view ['+@schemaName +'].[' + @objectName + ']')
end
fetch next from cur into @objectId, @schemaName, @objectName, @type, @parentObjId
end
close cur
deallocate cur
```
```
declare @objectId int, @objectName varchar(500), @schemaName varchar(500), @type nvarchar(30), @parentObjId int, @parentObjName nvarchar(500)
declare cur cursor
for
select obj.object_id, s.name as schema_name, obj.name, obj.type, parent_object_id
from sys.schemas s
inner join sys.sysusers u
on u.uid = s.principal_id
JOIN
sys.objects obj on obj.schema_id = s.schema_id
WHERE s.name = 'schema_name' and (type = 'p' or obj.type = 'v' or obj.type = 'u' or obj.type = 'f' or obj.type = 'fn')
order by obj.type
open cur
fetch next from cur into @objectId, @schemaName, @objectName, @type, @parentObjId
while @@fetch_status = 0
begin
if @type = 'p'
begin
exec('drop procedure ['+@schemaName +'].[' + @objectName + ']')
end
if @type = 'fn'
begin
exec('drop FUNCTION ['+@schemaName +'].[' + @objectName + ']')
end
if @type = 'f'
begin
set @parentObjName = (SELECT name from sys.objects WHERE object_id = @parentObjId)
exec('ALTER TABLE ['+@schemaName +'].[' + @parentObjName + ']' + 'DROP CONSTRAINT ' + @objectName)
end
if @type = 'u'
begin
exec('drop table ['+@schemaName +'].[' + @objectName + ']')
end
if @type = 'v'
begin
exec('drop view ['+@schemaName +'].[' + @objectName + ']')
end
fetch next from cur into @objectId, @schemaName, @objectName, @type, @parentObjId
end
close cur
deallocate cur
```