El script SQL compartido en esta página permite:
IMPORTANTE
Le ejecución de cada uno de los puntos mencionados depende de asignar valor "1" a las variables bandera:
MUY IMPORTANTE
No pongan valor 1 a la bandera @flagDropObjects porque esto provocará la eliminación de todos los objetos de la base de datos.
Este sitio web y el autor de esta nota no se hace responsable de los resultados obtenidos por la mala ejecución de este script.
declare @flagShowObjectsList bit = 1, @flagShowCommands bit = 1, @flagDropObjects bit = 0; declare @i int, @n int, @XSQL varchar(max); declare @TSQL TABLE (ROWID int identity(1, 1), NGROUP int, XSQL varchar(max), OBJ_NAME varchar(500)); -- Generación de comandos de eliminación de objetos set nocount on; insert into @TSQL (NGROUP, XSQL, OBJ_NAME) select 100 as NGROUP, 'alter table [' + e.name + '].[' + c.name + '] drop constraint [' + b.name + '];' as XSQL, a.[name] as [OBJ_NAME] from sys.objects a inner join sys.foreign_keys b on b.referenced_object_id = a.object_id inner join sys.objects c on c.object_id = b.parent_object_id inner join sys.schemas e on e.schema_id = c.schema_id union select 200 as NGROUP, 'drop function [' + b.name + '].[' + a.name + ']' as XSQL, a.[name] as [OBJ_NAME] from sys.objects a inner join sys.schemas b on b.schema_id = a.schema_id where a.type in ('fn', 'TF') and not a.name in ('fn_diagramobjects') union select 225 as NGROUP, 'drop proc [' + b.name + '].[' + a.name + ']' as XSQL, a.[name] as [OBJ_NAME] from sys.objects a inner join sys.schemas b on b.schema_id = a.schema_id where a.type = 'p' and not a.name in ('sp_upgraddiagrams', 'sp_helpdiagrams', 'sp_helpdiagramdefinition', 'sp_creatediagram', 'sp_renamediagram', 'sp_alterdiagram', 'sp_dropdiagram' ) union select 250 as NGROUP, 'drop view [' + b.name + '].[' + a.name + ']' as XSQL, a.[name] as [OBJ_NAME] from sys.objects a inner join sys.schemas b on b.schema_id = a.schema_id where a.type = 'v' union select 300 as NGROUP, 'drop table [' + b.name + '].[' + a.name + ']' as XSQL, a.[name] as [OBJ_NAME] from sys.objects a inner join sys.schemas b on b.schema_id = a.schema_id where a.type = 'u' and not a.name in ('sysdiagrams'); set nocount off; if (@flagShowObjectsList = 1) begin select * from sys.objects a where not a.[name] in ( 'sp_upgraddiagrams', 'sp_helpdiagrams', 'sp_helpdiagramdefinition', 'sp_creatediagram', 'sp_renamediagram', 'sp_alterdiagram', 'sp_dropdiagram', 'fn_diagramobjects', 'sysdiagrams' ) and not a.[type] in ( 'S', 'D', 'IT', 'PK', 'F', 'UQ', 'SQ') order by a.[type], a.[name]; end; if (@flagShowCommands = 1) select * from @TSQL order by NGROUP; if (@flagDropObjects = 1) begin set @i = 1; set @n = (select count(*) from @TSQL); while (@i <= @n) begin set @XSQL = (select XSQL from @TSQL where rowid = @i); exec (@XSQL); set @i = @i + 1; end; end; go