How to delete/drop all the tables from SQL Server Database without using Enterprise Manager?

How to delete/drop all the tables from SQL Server Database without using Enterprise Manager?

I tried using DROP Tables, Truncate Database, Delete and many more but it is not working. I want to delete all tables using Query Analyzer, i.e. through SQL Query.

Please help me out in this concern.

Nishith Shah

hi Nishith Shah

try this

EXEC sp_MSforeachtable @.command1 = "DROP TABLE ?"

this is a hidden SP in sql server, this will be executed for each table in the database you connected (you cant rollback this)

if u want to delete it from the command prompt try this

EXEC xp_cmdshell 'SQLCMD -U <user> -P <password> -Q 'EXEC sp_MSforeachtable @.command1 = "DROP TABLE ?" ' ,no_output

Best of luck.

Gurpreet S. Gill

|||Hi Gurpreet,
it worked man.......... thanx a lot for your reply!

Nishith Shah|||

Thanks man

|||Hi Gurpreet! once again.

you have shown me the perfect way to delete/drop all table using single SQL statement.

tell me if i just want to truncate/delete all the tables then how can i?

pls reply

Nishith|||

Hay man what you are asking for,if you just check my reply, the answere is there

ok, try this, this will delete/truncate all the Data from each table for in the database you connected

EXEC sp_MSforeachtable @.command1 = "DELETE FROM ?"

EXEC sp_MSforeachtable @.command1 = "TRUNCATE TABLE ?"

I too explain it now, as sp_MSforeachtable is Stored Procedure, that will execute for all the tables for database & @.command1 is variable which will run against each table for connected database, now whatever you will write in the double quotes, that will be act as a command for each table, where '?' is the name of the table.

try this, it will clear your comcepts

EXEC sp_MSforeachtable @.command1 = "SELECT * FROM ?" -- Selects all the rows form all the table

EXEC sp_MSforeachtable @.command1 = "PRINT '?'" --Just print the tables names with owner(dbo)

For more understanding, go for the MSDN or google, this is the right way.

If still you are confused do call me any time(I am an Indian, 24x7) at +91-99495-60051

Regards,

Thanks.

Gurpreet S. Gill

|||Hello Gurpreet,

thanks a lot for helping man and giving your cell # also. It worked again...
So, where r u working? as a?

do contact me anyhow on me.poison@.gmail.com or nishith82@.hotmail.com
atleast send me a blank email, i will understand its u.

thanks,
Nishith|||

You won't be able to run TRUNCATE against all tables if you have foreign keys references

Here is one way to circumvent that

-- First disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
DELETE FROM ?
else
TRUNCATE TABLE ?
'
GO

-- Now enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

Denis the SQL Menace
http://sqlservercode.blogspot.com/

|||

Thanks Denis, ya these things need to consider, before applying delete/truncate command.

Regards,

Thanks.

Gurpreet S. Gill

|||

HI people

I want to do this in MS Access database . Delete all tables . Is there a hidden SP here also ? or some other way . .

Plz help

|||

hi

i cant say anything about this, better to go for the MS-Access forum.

or

if you know the visual basic you can write the macro for that.

just check this link

http://www.codecomments.com/message725983.html

Regards,

thanks.

Gurpreet S. Gill