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

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

11 thoughts on “Using SQL query removes all tables data with schema | stored procedures | views | indexes”

  1. You really make it seem really easy along with your
    presentation however I in finding this topic to be really something which I feel
    I might never understand. It kind of feels too complex and extremely wide for me.
    I’m having a look forward to your subsequent put up, I will attempt to get the grasp of it!

    Reply
  2. First off I would like to say excellent blog! I had a quick question in which I’d like to ask if you do not mind.

    I was interested to find out how you center
    yourself and clear your mind prior to writing.
    I’ve had trouble clearing my mind in getting my ideas out.
    I do enjoy writing but it just seems like the first 10
    to 15 minutes are wasted just trying to figure out how to begin. Any recommendations or tips?
    Many thanks!

    Reply
  3. Magnificent items from you, man. I have be mindful your stuff previous to and
    you are simply extremely fantastic. I really like what you have received right
    here, really like what you’re saying and the way in which by which
    you are saying it. You are making it entertaining and you still take care of to stay it wise.
    I can not wait to read far more from you. That is really
    a great web site.

    Reply
  4. Greetings from Ohio! I’m bored to death at work so I decided to check out your site on my iphone during lunch break.
    I really like the info you provide here and can’t wait to take a look when I get home.

    I’m surprised at how fast your blog loaded on my phone ..

    I’m not even using WIFI, just 3G .. Anyways, awesome site!

    Reply

Leave a Comment