Hi All,

How can i delete all the tables in a DATABASE with a single shot!!

Thanx in advance

Well...what i do is may not be the right way)

Just delete the database.......and create a new database........else it will start asking for so many dependencies)

cheers!

Benny,

In a database TABLES, VIEWS, FUNCTIONS, PROCEDURES, TRIGGERS, etc. are there! So I have to delete only TABLES. If I drop the database means I want to recreated all those objects.

I need to delete TABLES alone.

Thanx in advance

another simple way

generate SQL script of the database, select "all tables", select "generate drop command for each object" check box only (not the create one). you should have a script with all drop commands. run it

Hi

Its not a appropriate way to drop! plz..

DELETE FROM sysobjects WHERE xtype='U'
Need to check the option "update system catalogs" for the server properties prior to execute the statement.

DO NOT delete anything from sysobjects. That's insane.

If you want to delete the tables (in SQL Server lingo, this just means delete the data out of all of them).

DECLARE
@sql VARCHAR(4000),
@int_counter INT,
@int_max INT

DECLARE @tables TABLE(
ident INT IDENTITY(1,1) PRIMARY KEY,
table VARCHAR(256))

INSERT @tables(table)
SELECT name FROM sysobjects WHERE xtype = 'U'

SELECT
@int_counter = 1,
@int_max = (SELECT MAX(ident) FROM @tables))

WHILE @int_counter <= @int_max
BEGIN

SELECT @sql = 'DELETE ' + table
FROM @tables WHERE ident = @int_counter

SELECT @int_counter = @int_counter + 1
END

This will only work if you don't have foreign keys though, so you would have to make a similar procedure to drop and recreate those. You can find one on SQLServerCentral.com though.

If you really want to just drop all the tables, change the DELETE to DROP TABLE, and you are good to go. NEVER edit the system tables as a shortcut. It's dangerous; and there are too many good scripts someone else has already written to be doing that.

"Its not a appropriate way to drop!"???????

What they heck WOULD be an appropriate way to drop all the tables in a database with dependent procedures, views, functions, and perhaps even triggers?

I have trouble believing what you are doing is appropriate or necessary in the first place!

If he's dropping the tables to just recreate them, the biggest problem will be the foreign keys if he has them.

He is just askin to delete ALL tables. Why r u scared of the foreign keys, obviously he would have the script for recreating the tables which would include the relationships too.

Again, What For???

If he can run a script to restore them, presumably he could run upalsen's script solution to drop them...

He says that he wanna delete all tables in one shot, just for fun i guess, therefore one delete query in sysobjects would satiate his desires rather to select all tables and then choosing include drop tables statement and then running the drop table commands for each table.
However, it's clear that playin with system catalogs is not so wise. U r right indeed.

Saravanan.R

why do you want to do this
it may be that we may have an alternate solution for your problem other than deleting all of your tables

for example if you want to just remove all of the data from your tables without dropping them, then try the truncate table statement.

[Books Online] Truncate Table

ps if you ever directly modify a system table, we will run you out of town and burn your castle like a group of villagers chasing the frankestein monter

Hi All,

Sorry I have to DELETE (DROP ALL TABLES) in a single shot!

You think he wants to drop all the tables in his database "just for fun"?

What the heck are YOUR hobbies?