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
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!
I think so there is no complex thing, easy to copy and paste and run it, it will delete all views, sp, tables, indexes etc.
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!
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.
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!
It is in point of fact a nice and helpful piece of information. I am satisfied that you just shared this useful info with us.
Please keep us informed like this. Thanks for sharing.
Thank you for the auspicious writeup. It in fact was
a amusement account it. Look advanced to far added agreeable from you!
However, how can we communicate?
great submit, very informative. I ponder why the other experts of this sector do not realize this.
You should continue your writing. I’m sure, you have
a huge readers’ base already!
I just could not depart your site before suggesting that I extremely loved the usual info a person supply
in your visitors? Is going to be again often to check out new posts
Greetings! Very useful advice in this particular article!
It’s the little changes that will make the greatest changes.
Thanks a lot for sharing!
Everyone loves it when people come together and share views.
Great blog, continue the good work!