How to Delete All tables' records?

[soclose]

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.

[dglienna]

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...

[Teranoz]

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.

[davide++]

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:


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


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

[soclose]

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++.

[Shuja Ali]

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

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

[davide++]

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

[adambom]

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

TRUNCATE TABLE @TableName

[soclose]

(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?



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?

[davide++]

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


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


you'll get something like this


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.



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.

[soclose]

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

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



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!


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


So the result is


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


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

[ComITSolutions]

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

[Madhi]

Refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/truncate-all-tables-part-ii.aspx

[soclose]

Hi, thanks all. I achieve more Knowledge.

[soclose]

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. :)