"Clean" Tables, Stored Procedures, Function, View. stored in different object schema but not touch the system objects

CleanDatabaseWithoutDropDB.sql

/* Drop all non-system stored procs */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
DECLARE @schem varchar(128)
DECLARE @CantidadIteraciones INT
--SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])
SELECT  TOP 1 @schem = SCHEMA_NAME(schema_id), @name = [name] FROM sys.procedures ORDER BY [name]
SET @CantidadIteraciones = ( SELECT count(*) from sys.procedures )

WHILE @CantidadIteraciones>0
	BEGIN
		SELECT @SQL = 'DROP PROCEDURE ['+LTRIM(RTRIM(@schem))+'].[' + RTRIM(@name) +']'
		--print 'DROP PROCEDURE ['+LTRIM(RTRIM(@schem))+'].[' + RTRIM(@name) +']'
		EXEC (@SQL)
		PRINT 'Dropped Procedure: '+ @schem +'.'+@name
		SELECT TOP 1 @schem = SCHEMA_NAME(schema_id), @name = [name] FROM sys.procedures  WHERE [name] > @name ORDER BY [name]
		SET @CantidadIteraciones= @CantidadIteraciones - 1
		--SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
	END
GO

/* Drop all views */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
DECLARE @schem varchar(128)
DECLARE @CantidadIteraciones INT
--SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])
SELECT  TOP 1 @schem = SCHEMA_NAME(schema_id), @name = [name] FROM sys.views ORDER BY [name]
SET @CantidadIteraciones = ( SELECT count(*) from sys.views )

WHILE @CantidadIteraciones>0
	BEGIN
		SELECT @SQL = 'DROP VIEW ['+LTRIM(RTRIM(@schem))+'].[' + RTRIM(@name) +']'
		--print 'DROP PROCEDURE ['+LTRIM(RTRIM(@schem))+'].[' + RTRIM(@name) +']'
		EXEC (@SQL)
		PRINT 'Dropped View: '+ @schem +'.'+@name
		SELECT TOP 1 @schem = SCHEMA_NAME(schema_id), @name = [name] FROM sys.views  WHERE [name] > @name ORDER BY [name]
		SET @CantidadIteraciones= @CantidadIteraciones - 1
	END
GO
/* Drop all functions */
--select * from sys.all_objects  WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND object_id > 0 ORDER BY [name]

DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
DECLARE @schem varchar(128)
DECLARE @CantidadIteraciones INT

--SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])
SELECT  TOP 1 @schem = SCHEMA_NAME(schema_id), @name = [name] FROM sys.all_objects  WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND object_id > 0 ORDER BY [name]
SET @CantidadIteraciones = ( SELECT count(*) FROM sys.all_objects  WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND object_id > 0 )

WHILE @CantidadIteraciones>0
BEGIN
    SELECT @SQL = 'DROP FUNCTION ['+LTRIM(RTRIM(@schem))+'].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Function: '+ @schem +'.'+ @name
    SELECT  TOP 1 @schem = SCHEMA_NAME(schema_id), @name = [name] FROM sys.all_objects  WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND object_id > 0 ORDER BY [name]
	SET @CantidadIteraciones= @CantidadIteraciones - 1
END
GO

/* Drop all Foreign Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)
DECLARE @schem varchar(128)
DECLARE @CantidadIteraciones INT
DECLARE @CantidadRelacionesForaneasTabla INT
--SELECT TOP 1 @constraint=name,@name=OBJECT_NAME(parent_object_id),@schem = SCHEMA_NAME(schema_id) FROM sys.foreign_keys ORDER BY OBJECT_NAME(parent_object_id)
SELECT TOP 1 @name=OBJECT_NAME(parent_object_id),@schem=OBJECT_SCHEMA_NAME(parent_object_id), @CantidadIteraciones =COUNT(*) OVER () FROM sys.foreign_keys GROUP BY OBJECT_NAME(parent_object_id),OBJECT_SCHEMA_NAME(parent_object_id) ORDER BY OBJECT_NAME(parent_object_id)
--WHERE OBJECT_NAME(parent_object_id)>'DEPENDENCIA_OPCION'

WHILE @CantidadIteraciones>0
BEGIN
	--PRINT @name+'>>>>'
	SELECT TOP 1 @constraint=name,@CantidadRelacionesForaneasTabla= count(*) over(partition by parent_object_id) 
	FROM sys.foreign_keys where OBJECT_NAME(parent_object_id) = @name ORDER BY object_id
	--print @CantidadRelacionesForaneasTabla
    WHILE @CantidadRelacionesForaneasTabla > 0
    BEGIN
        SELECT @SQL = 'ALTER TABLE ['+LTRIM(RTRIM(@schem))+'].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
        EXEC (@SQL)
        PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + '['+LTRIM(RTRIM(@schem))+'].[' + RTRIM(@name) +']'
		SELECT TOP 1 @constraint=name FROM sys.foreign_keys where OBJECT_NAME(parent_object_id) = @name and name>@constraint ORDER BY object_id
		SET @CantidadRelacionesForaneasTabla = @CantidadRelacionesForaneasTabla -1
    END
	
	SELECT TOP 1 @name=OBJECT_NAME(parent_object_id),@schem=OBJECT_SCHEMA_NAME(parent_object_id)
	FROM sys.foreign_keys WHERE OBJECT_NAME(parent_object_id)>@name	GROUP BY OBJECT_NAME(parent_object_id),OBJECT_SCHEMA_NAME(parent_object_id) ORDER BY OBJECT_NAME(parent_object_id)

	SET @CantidadIteraciones= @CantidadIteraciones -1
END
GO
/* Drop all Primary Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)
DECLARE @schem varchar(128)
DECLARE @CantidadIteraciones INT
DECLARE @CantidadPKTabla INT

select TOP 1 @name=OBJECT_NAME(parent_object_id),@CantidadIteraciones =count(*) over(),@schem=SCHEMA_NAME(schema_id) from sys.key_constraints  order by OBJECT_NAME(parent_object_id)

WHILE @CantidadIteraciones>0
BEGIN
	select TOP 1 @constraint=name,@CantidadPKTabla = count(*) over() 
	from sys.key_constraints where OBJECT_NAME(parent_object_id) = @name and SCHEMA_NAME(schema_id)=@schem order by OBJECT_NAME(parent_object_id)
	WHILE @CantidadPKTabla>0
    BEGIN
        SELECT @SQL = 'ALTER TABLE ['+@schem+'].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'
		EXEC (@SQL)
        PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + '['+@schem+'].[' + RTRIM(@name) +']'
		select TOP 1 @constraint=name from sys.key_constraints where OBJECT_NAME(parent_object_id) = @name and SCHEMA_NAME(schema_id)=@schem and name>@constraint order by OBJECT_NAME(parent_object_id)
		SET @CantidadPKTabla = @CantidadPKTabla -1
    END
	print @name
	select TOP 1 @name=OBJECT_NAME(parent_object_id),@schem=SCHEMA_NAME(schema_id) 
	FROM sys.key_constraints 
	WHERE OBJECT_NAME(parent_object_id)>@name order by OBJECT_NAME(parent_object_id)
	SET @CantidadIteraciones = @CantidadIteraciones -1
END
GO
--
/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
DECLARE @schem varchar(128)
DECLARE @CantidadIteraciones INT

select TOP 1 @name=name, @schem=SCHEMA_NAME(schema_id), @CantidadIteraciones = COUNT(*)over() from sys.tables order by [name]

WHILE @CantidadIteraciones>0
BEGIN
    SELECT @SQL = 'DROP TABLE ['+@schem+'].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Table: ['+@schem+'].[' + RTRIM(@name) +']'
	select TOP 1 @name=name, @schem=SCHEMA_NAME(schema_id) from sys.tables where name>@name order by [name]
	SET @CantidadIteraciones = @CantidadIteraciones -1
END
GO