How To Delete All Tables

Hi All,
How can i delete all the tables in a DATABASE with a single shot!!
Thanx in advanceWell...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!


Originally posted by Saravanan.R
Hi All,

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

Thanx in advance|||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.|||Originally posted by Saravanan.R
Hi All,

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

Thanx in advance

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!|||Originally posted by blindman
"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?|||You can drop all the tables in various different ways. The solution that is right depends on what you are trying to achieve by dropping the tables.

What is it you are trying to achieve? And please don't say "I have to DELETE (DROP ALL TABLES) in a single shot!", we want to know why...|||Sorry Blindman,

After migrating the Oracle to SQLServer I have to compile the SQLServer Objects in a Database. While compiling the Object I will shows error! bec' already Objects were created! Instead of that, before compiling it I want to DROP ALL TABLES. I have thousands of table in my DATABASE. SO I need this statement IF POSSIBLE!. I don't want to DROP THE DATABASE , Thats why am put Q in forums is there any anternate way to DROP TABLES.?

Its not a hobby to post question in FORUMS like this! Sorry Blindman!!.|||Ok, i think there could be some scenarios where one may need to use system-catalogs, e.g, if i need to drop the tables in a DB with names DELETED at the end and i've more that hunderds of table in the database.
What would i do according to derrickleggett's wise advise that i would go to enterprise-manager, make a script of creation and deletion of these tables by looking at each table and marking only the DELETED tables to be included in the script, right. After spending a lot of time i would create the script and then go to query-analyzer and run the DROP TABLES section for these tables. How safe is it, but took very much time, right?
But if i know what i wanna do, i would rather choose:
DELETE FROM sysobjects WHERE name like '%DELETED' AND xtype='U'

It would delete the tables in ONE SHOT in few seconds.
As mentioned; although it's not a good technique to play with sys-catalogs but sometimes there's no other way out.
Now one can comment that why do i need to delete all the DELETED tables in ONE SHOT:D

Howdy!!|||You can always experiment with sp_msforeachtable, but really, you NEED a script that clears the database. In fact, if this is "your baby" you need a good dozen or two of db maintenance scripts, including dropping, creating, etc. You have to be a lazy DBA to have good stuff for everything, but you can't be "that" lazy...|||OK, I guess I understand why you want to do this (thousands of tables? That's a whole other problem...). But why must it be a single statement? I don't see anything in your requirement that prevents you from running multiple statements that load a list of tables into temporary storage and then loops through issueing dynamic DROP statements. This would seem to be what you want, but might require a half-dozen different statements to set up the temporary table(s), populate them, and then run your DROP loop.|||Actually the problem is:
Saravanan.R wants it in ONE SHOT. That's it.:o|||If that was the case, upalsen's script solution would work fine. It may be a long script, but it would be executed "in one shot". :o :rolleyes:|||Hmmm, I want a house and a boat in Dominican Republic...Anyone has a script so that I can get it "IN ONE SHOT"??!!

Also, if DRI is present, along with a script that generates DROPs for tables Saravanan.R will have to handle removing FK constraints.

Here's how I'd do it:

- Create a view using the following code:

create view dbo.vw_DropTables (stmt) as
select
'alter table ' + object_name(id) + ' drop constraint ' +
object_name(constid) + char(13)+char(10)+
"if @.@.error != 0 raiserror ('Failed to drop " +
object_name(constid) + " constraint!', 15, 1)"+
char(13)+char(10)+'go'
from sysconstraints where objectproperty(constid, 'IsForeignKey') = 1
union
select 'drop table ' + name + char(13)+char(10)+
"if @.@.error != 0 raiserror ('Failed to drop " +
object_name(id) + " table!', 15, 1)"+
char(13)+char(10)+'go'
from sysobjects where objectproperty(id, 'IsMSShipped') = 0
and objectproperty(id, 'IsTable') = 1
go

- Create a batch file with the following commands:

bcp <your_db_name>.dbo.vw_DropTables out DropTables.SQL
-S <your_server_name> -T -c
if not exist DropTables.SQL goto ErrorHandler
osql -S <your_server_name> -E -Q"DropTables.SQL" -b
if errorlevel 1 goto ErrorHandler
exit

ErrorHandler:
echo ERROR!|||insert into AccountBalances (AccountHolder, Balance)
select 'rdjabarov', sum('Balance')
from AccountBalances

You owe me a maragarita.|||Mr. Lindman,

I didn't quite get your reply. But that may be the language barrier, huh?!

...And your query will bomb:

Server: Msg 409, Level 16, State 2, Line 1
The sum or average aggregate operation cannot take a varchar data type as an argument.

You owe me a maragarita.
Never heard of such drink...Is it popular in OH?|||This script will work also:

DECLARE
@.int_counter INT,
@.int_max INT,
@.txt_type CHAR(1),
@.txt_object VARCHAR(256),
@.txt_object_parent VARCHAR(256),
@.txt_sql VARCHAR(4000)

DECLARE @.objects TABLE(
ident INT IDENTITY(1,1) PRIMARY KEY,
object VARCHAR(256),
object_parent VARCHAR(256),
object_type CHAR(1))

INSERT @.objects(
object,
object_parent,
object_type)

SELECT CONSTRAINT_NAME, TABLE_NAME, 'C'
FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
UNION ALL
SELECT name, '','U'
FROM sysobjects
WHERE
xtype = 'U'
AND name NOT LIKE 'dt_%'

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

WHILE @.int_counter <= @.int_max
BEGIN

SELECT
@.txt_type = o.object_type,
@.txt_object = o.object,
@.txt_object_parent = o.object_parent
FROM
@.objects o
WHERE
o.ident = @.int_counter

IF @.txt_type = 'C'
BEGIN
SELECT @.txt_sql = 'ALTER TABLE ' + @.txt_object_parent + ' DROP CONSTRAINT ' + @.txt_object
END
ELSE
BEGIN
SELECT @.txt_sql = 'DROP TABLE ' + @.txt_object
END

PRINT @.txt_sql

SELECT @.int_counter = @.int_counter + 1
END

and, Talat this isn't a wrestling match to show who's the most manly man in the room. It's just a forum to help people. If you delete all the tables from sysobjects, you just messed up the entire database. You didn't do anything in one shot except shoot yourself in the head.

People should rarely mess with the system catalog, especially not in your case when it's obvious you have no idea how they work. Do you have any useful script to post?|||Good lord, I even had syntax errors in my drink order.

Bartender, call a cab to drive me home.|||use DominicanRepublic
go
exec sp_changeobjectowner 'house', 'rdjabarov'
go
exec sp_changeobjectowner 'boat', 'rdjabarov'
go|||Hi All,

DROP TABLE works with " derrickleggett " solution.

Thanx to all.|||Good for you! Why don't you hire him? He'll provide you with solutions!|||You don't have to hire me. Just send me a check.|||I wouldn't. At a minimum your script will drop dtproperties, and the result still needs to be dealt with (copy/paste into another script). And what's up with looping? Didn't you see BCP...OUT posted earlier?|||I'm just amused by the idea of India outsourcing to Kansas City, MO.|||Originally posted by rdjabarov
I wouldn't. At a minimum your script will drop dtproperties, and the result still needs to be dealt with (copy/paste into another script). And what's up with looping? Didn't you see BCP...OUT posted earlier?

Yeah, that's because I typed AND name NOT LIKE 'dt_%' instead of AND name NOT LIKE 'dt%'. You could have just said that instead.

Also, if you change the PRINT @.txt_sql to EXEC(@.txt_sql) it will run it instead of printing it. I figured most people would figure that out.

How can i delete all the tables in a DATABASE with a single shot!!
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
That's the original post and all he's really trying to do.

The looping loops through those @.txt_sql statements and either prints them off or executes them one by one. It starts at 1 and goes until it gets to the end. It does this by using WHILE and incrementing a counter variable.|||If his company hires the smart chap, this fellow will be fired:)|||Hi derrickleggett,

Yah! In the place of PRINT I replaced the EXEC Statement to EXECUTE that. It works perfectly.

I think that, We can use to DELETE all PROCEDURE instead of putting 'P' for 'U'.|||Yeah, if you want to do that though, just edit it to except an object type, so you can pass in the object type to it.|||Hi,

I have modified to DELETE all the PROCEDURES, VIEWS & now am trying to do function too.|||Originally posted by Saravanan.R
Hi,

I have modified to DELETE all the PROCEDURES, VIEWS & now am trying to do function too.

WHERE xtype IN ('IF','FN','TF')

Why not just drop the whole database. lol|||...or reformat the drive. That's an easy way to drop all tables, procedures, views, and functions in all databases IN ONE SHOT!|||Hi Blindman,

have u forgot the article that i was posted on 04-05-04 10:58 !!!!!!!???

Plz read the article i was posted on 04-05-04 10:58

Thanx for all|||Hey ... just tell us what the hell are you trying to do ... we all are pretty confused ...|||Hi All,

I want to Drop Tables, Procedures, Views ... Because

after migrating the Oracle to SQLServer I have to compile the SQLServer Objects in a particular Database(Eg.sample). While compiling the Objects, if already Objects were exists! I will shows the error! For this I want to drop objects in my DATABASE.

have u got it!!!|||I still don't get it ...

I think I am getting more confused with the terminology you are using ... can you explain it in simpler words ??|||Originally posted by derrickleggett
Yeah, that's because I typed AND name NOT LIKE 'dt_%' instead of AND name NOT LIKE 'dt%'. You could have just said that instead.

Also, if you change the PRINT @.txt_sql to EXEC(@.txt_sql) it will run it instead of printing it. I figured most people would figure that out.

How can i delete all the tables in a DATABASE with a single shot!!
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
That's the original post and all he's really trying to do.

The looping loops through those @.txt_sql statements and either prints them off or executes them one by one. It starts at 1 and goes until it gets to the end. It does this by using WHILE and incrementing a counter variable. Hey, I understand your code, no need to explain what WHILE loop does and what it starts with ;) I don't think you understood mine though! But hey, I've done your loops and got away from it, because there are more elegant ways to do it.

And I do agree with others who question this "DROP IN ONE SHOT" thing. I assume he has the schema script as well, the one that does the reverse, - CREATE ALL OBJECTS IN ONE SHOT...Can you do that? I can ;) But having this knowledge completely dismisses the DROP-ALL approach as absolutely inadequate, regardless of what you're moving your database from, - Oracle, Horacle, Shmoracle, and everything else :D|||Originally posted by Enigma
I still don't get it ...

I think I am getting more confused with the terminology you are using ... can you explain it in simpler words ??

It sounds like part of his import process is validating the actual compiling of objects as a validation process. This actually makes quite a bit of sense.

For instance, if I want to import one subject area at a time, I test the import of those pieces in stages. The first step is to go to my sample database and delete those objects if they exist from a previous test. I only wan to delete the objects I'm testing and don't want to recreate the database.

I then test the compiling of those objects. I then test the import of the data. After the tests validate my process, I run it on the server I'm importing to.

I'm not sure if this is exactly what he's doing, but I could understand why he would want to do it after doing data conversion for so many years. The more validation you have the better.|||Originally posted by blindman
...or reformat the drive. That's an easy way to drop all tables, procedures, views, and functions in all databases IN ONE SHOT!

DELETE * FROM sysdatabases.
It's more easy!!!!!!!:p|||Talat, if you ever come near my server room I will have you arrested. lol|||Hi Enigma,

I have more than 500tables, procedures .. in my SQLServer DATABASE ( Example: saravanan). I have to fix error in my SQL-Server Tables, Transaction SQL Codes afte migrating Oracle to SQLServer. While compiling SQLServer Tables, Procedure.. On that time If the tables, procedures already created in my SARAVANAN database. It will show errors like this " There is already an object named 'TEMPFUNC' in the database. "

For this purpose I have to drop all the tables , procedures before my compilation.|||Anbody know where I can get my Shmoracle certification? I hear it's pretty tough.|||Which track u r interested in, sir, i complted OCP-Developer a few months back and pursuing DBA track now. Both r rather easy. I'm gonna give 70-228 on 20th of April, wish me good luck plz u all, especially Sarvanan!!|||Hi TALAT,

DO THE BEST.

Luv
Saravanan.R|||Originally posted by blindman
Anbody know where I can get my Shmoracle certification? I hear it's pretty tough. I am developing a class for Horacle now. Hoping to complete it by this weekend and start with Shmoracle one.|||Saravanan, I still think you may be going about this in the wrong way. If you are getting errors saying a particular object already exists when you run your script, then your script should either:

1) Drop each object before attempting to create it.
or B) Check to see whether the object already exists before attempting to create it.

Dropping all the objects is a rather blunt instrument for the operation, IMHO.|||Lindman does objects...|||Originally posted by blindman
Saravanan, I still think you may be going about this in the wrong way. If you are getting errors saying a particular object already exists when you run your script, then your script should either:

1) Drop each object before attempting to create it.
or B) Check to see whether the object already exists before attempting to create it.

Dropping all the objects is a rather blunt instrument for the operation, IMHO. ...and finally III) Check to see if object exists. If it does, - drop it. And then create it or whatever other sick things you have in mind ;)
D) Do a separate script that has to be based on dependency between objects. Another way of going about doing it is either dropping FK constraints or disabling them. But either way, you need to do it in a thorough, consistent, and error-preventive way, not in a "ONE SHOT" fashion.|||rdjabarov does posts|||Robert does posts!|||yes ofcourse i do.

Thanx all.