Using SQL query removes all tables data with schema | stored procedures | views | indexes

by

Rather then delete one by one remove all data and schema from database using single SQL query.

Mostly on shared hosting where we are not able to check SQL server database and table views because of SQL server version, sometimes our SQL version is low or some time they version is high. So we are not able to see SQL Object Explorer.

So I use mostly this query to delete everything from the database. It will remove Tables, Stored procedures, Views, indexes, etc.



DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])

WHILE @name is not null
BEGIN
    SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Procedure: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END
 
 
DECLARE @name1 VARCHAR(128)
DECLARE @SQL1 VARCHAR(254)

SELECT @name1 = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])

WHILE @name1 IS NOT NULL
BEGIN
    SELECT @SQL1 = 'DROP VIEW [dbo].[' + RTRIM(@name1) +']'
    EXEC (@SQL1)
    PRINT 'Dropped View: ' + @name1
    SELECT @name1 = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name1 ORDER BY [name])
END
  
/* Drop all functions */
DECLARE @name2 VARCHAR(128)
DECLARE @SQL2 VARCHAR(254)

SELECT @name2 = (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])

WHILE @name2 IS NOT NULL
BEGIN
    SELECT @SQL2 = 'DROP FUNCTION [dbo].[' + RTRIM(@name2) +']'
    EXEC (@SQL2)
    PRINT 'Dropped Function: ' + @name2
    SELECT @name2 = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name2 ORDER BY [name])
END
 
/* Drop all Foreign Key constraints */
DECLARE @name3 VARCHAR(128)
DECLARE @constraint3 VARCHAR(254)
DECLARE @SQL3 VARCHAR(254)

SELECT @name3 = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)

WHILE @name3 is not null
BEGIN
    SELECT @constraint3 = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name3 ORDER BY CONSTRAINT_NAME)
    WHILE @constraint3 IS NOT NULL
    BEGIN
        SELECT @SQL3 = 'ALTER TABLE [dbo].[' + RTRIM(@name3) +'] DROP CONSTRAINT [' + RTRIM(@constraint3) +']'
        EXEC (@SQL3)
        PRINT 'Dropped FK Constraint: ' + @constraint3 + ' on ' + @name3
        SELECT @constraint3 = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint3 AND TABLE_NAME = @name3 ORDER BY CONSTRAINT_NAME)
    END
SELECT @name3 = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
END
 

/* Drop all Primary Key constraints */
DECLARE @name4 VARCHAR(128)
DECLARE @constraint4 VARCHAR(254)
DECLARE @SQL4 VARCHAR(254)

SELECT @name4 = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)

WHILE @name4 IS NOT NULL
BEGIN
    SELECT @constraint4 = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name4 ORDER BY CONSTRAINT_NAME)
    WHILE @constraint4 is not null
    BEGIN
        SELECT @SQL4 = 'ALTER TABLE [dbo].[' + RTRIM(@name4) +'] DROP CONSTRAINT [' + RTRIM(@constraint4)+']'
        EXEC (@SQL4)
        PRINT 'Dropped PK Constraint: ' + @constraint4 + ' on ' + @name4
        SELECT @constraint4 = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint4 AND TABLE_NAME = @name4 ORDER BY CONSTRAINT_NAME)
    END
SELECT @name4 = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
 

/* Drop all tables */
DECLARE @name5 VARCHAR(128)
DECLARE @SQL5 VARCHAR(254)

SELECT @name5 = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])

WHILE @name5 IS NOT NULL
BEGIN
    SELECT @SQL5 = 'DROP TABLE [dbo].[' + RTRIM(@name5) +']'
    EXEC (@SQL5)
    PRINT 'Dropped Table: ' + @name5
    SELECT @name5 = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name5 ORDER BY [name])
END

Comments are closed.