-
July 28th, 2008, 11:11 PM
#1
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.
-
July 28th, 2008, 11:14 PM
#2
Re: How to Delete All tables' records?
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...
-
July 29th, 2008, 03:08 AM
#3
Re: How to Delete All tables' records?
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.
-
July 29th, 2008, 05:43 AM
#4
Re: How to Delete All tables' records?
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...
-
July 30th, 2008, 01:40 AM
#5
Re: How to Delete All tables' records?
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++.
-
July 30th, 2008, 02:41 AM
#6
Re: How to Delete All tables' records?
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'
-
July 30th, 2008, 05:49 AM
#7
Re: How to Delete All tables' records?
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
-
July 31st, 2008, 06:50 AM
#8
Re: How to Delete All tables' records?
The fastest way to delete all records from a table is:
TRUNCATE TABLE @TableName
-
August 1st, 2008, 01:08 AM
#9
Re: How to Delete All tables' records?
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?
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?
-
August 1st, 2008, 04:54 AM
#10
Re: How to Delete All tables' records?
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.
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.
-
August 7th, 2008, 05:40 AM
#11
Re: How to Delete All tables' records?
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'
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
Delete From Customers
Delete From Products
Delete From Store
....
This is a quick way to get all tables' names and their delete statements. Great!
-
August 19th, 2008, 09:32 AM
#12
Re: How to Delete All tables' records?
If SQL SERVER is the Database engine.
You can do many things dynamically using sp_execresultset system stored procedure..
Encourage the efforts of fellow members by rating
Lets not Spoon Feed and create pool of lazy programmers
- ComIT Solutions
-
September 8th, 2008, 04:23 AM
#13
Re: How to Delete All tables' records?
Madhivanan
Failing to plan is Planning to fail
-
September 9th, 2008, 01:49 AM
#14
Re: How to Delete All tables' records?
Hi, thanks all. I achieve more Knowledge.
-
September 29th, 2008, 10:04 PM
#15
Re: How to Delete All tables' records?
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|