How to Delete All tables' records?

Hi, I want to delete all tables' all rows by one query execution. so i need to write "Delete From tableName" again and again, as the numbers of tables. Is there any keyword or way to delete all rows of a database consisting more than 20 tables? Thanks.

that sounds like it's pretty dangerous to be posting on the web. kind of like how do i delete all files on my system? we don't answer things like that.

You might want to 'find all the tables' in a database, maybe...

You cant do that in just one query.
Instead of having to type the query for every table over and over again just make a small php script that does it.

Hi all
You didn't say what db you're using, but in any case there isn't a SQL command that cleans the whole database (as Teranoz pointed out). To solve this problem I used to create a script that contains the DELETE commands for all tables; I get this script writing a query on tables of dictionary that builds the commands.
For example, with Oracle database you can write something like this:

Code:

---

SELECT 'DELETE FROM ' || OBJECT_NAME || ';'
  FROM USER_OBJECTS
 WHERE OBJECT_TYPE = 'TABLE'

---

Of course, you need to run the commands in the correct order...

Thanks All. I'd like to make this delete process in MS SQL Server 2005. I tried to search the Delete syntax such as Oracle. But in MS SQL, I did not find User_Objects and only found Object_Type. If MS SQL has some way, pls tell me it, davide++.

This query in SQL 2005 will give you the names of all the tables

PHP Code:

---

Select Object_name(object_id) from sys.objects where type = 'U' 


---

Hi all.

Shuja answered to your question. Now you have to change the query to get the DELETE commands (you can use the string concatenation operator), then you'll copy&paste the result of the query in a file script, and finally you'll run the script. Remember that the commands must be written in the correct order, which is established by the constrains between the tables

The fastest way to delete all records from a table is:

TRUNCATE TABLE @TableName

Quote:

---

Originally Posted by davide++

(you can use the string concatenation operator), then you'll copy&paste the result of the query in a file script, and finally you'll run the script.

---

Hi davide++, I'm not clear above quote. Do u mean that I need to write Delete statement for each table that this tablename is got from the result of the query, one after one? So, where should I use string concatenation operator?

Quote:

---

Originally Posted by davide++

Remember that the commands must be written in the correct order, which is established by the constrains between the tables

---

I got clear point above last quote :) You mean that I should delete first Child Tables. Thanks davide++

I find another way to delete all tables' records. That is running the Generate Script file, not backup file. So we get a Blank database.

When we give a blank database to Users if there is no need to have any data, how do we generate it?

Quote:

---

Originally Posted by soclose

Hi davide++, I'm not clear above quote. Do u mean that I need to write Delete statement for each table that this tablename is got from the result of the query, one after one? So, where should I use string concatenation operator?

---

Yes, it's simply a trick; you should use the concatenation operator to build the DELETE command in a query, as I wrote above. For example, in Oracle the concatenation operator is "||" and USER_OBJECTS is the table that contains the schema objects, so running the query

Code:

---

SELECT 'DELETE FROM ' || OBJECT_NAME || ';'
  FROM USER_OBJECTS
 WHERE OBJECT_TYPE = 'TABLE'

---

you'll get something like this

Code:

---

DELETE FROM T_CONTRATTO;                                                                            DELETE FROM V_CLIENTE;                                                                              DELETE FROM V_MIGRAZIONE;                                                                            DELETE FROM V_PS_CTR_LEV;                                                                            DELETE FROM V_PS_SITE_LEV;                                                                          DELETE FROM V_SEDE;

---

Then you can get the deletion script by copy&paste these commands. I don't know the syntax of commands to do this in your DBMS, but this is the idea.


[QUOTE]
I got clear point above last quote :) You mean that I should delete first Child Tables. Thanks davide++
[/CODE]

Correct.

Quote:

---

I find another way to delete all tables' records. That is running the Generate Script file, not backup file. So we get a Blank database.

When we give a blank database to Users if there is no need to have any data, how do we generate it?

---

Yes, in this way you'll obtain a new database, and you have to install the new database every time.

Quote:

---

Originally Posted by Shuja Ali

This query in SQL 2005 will give you the names of all the tables

PHP Code:

---

Select Object_name(object_id) from sys.objects where type = 'U' 


---

---

Quote:

---

Yes, it's simply a trick; you should use the concatenation operator to build the DELETE command in a query, as I wrote above. For example, in Oracle the concatenation operator is "||" and USER_OBJECTS is the table that contains the schema objects, so running the query


Code:

SELECT 'DELETE FROM ' || OBJECT_NAME || ';'
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'TABLE'

you'll get something like this


Code:

DELETE FROM T_CONTRATTO; DELETE FROM V_CLIENTE; DELETE FROM V_MIGRAZIONE; DELETE FROM V_PS_CTR_LEV; DELETE FROM V_PS_SITE_LEV; DELETE FROM V_SEDE;

---

Hi All! Thanks to davide++ and Shuja Ali. I get their ideas based on their quote hints!

PHP Code:

---

Select 'Delete From ' + Object_name(object_id) from sys.objects where type = 'U' 


---

So the result is

Quote:

---

Delete From Customers
Delete From Products
Delete From Store
....

---

This is a quick way to get all tables' names and their delete statements. Great!

If SQL SERVER is the Database engine.
You can do many things dynamically using sp_execresultset system stored procedure..

Refer
http://sqlblogcasts.com/blogs/madhiv...s-part-ii.aspx

Hi, thanks all. I achieve more Knowledge.

Hi All. When we want to make a current used database to be blank and all identity columns set to 1, firstly we generate this database script by "Generate Scripts", drop this database, create this database again as a new one, and execute the previous script on it. It will be ok for a re-identity and blank database. I said about it before but not in detail. Here is step by step. Just share our knowledge. :)