CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2005
    Posts
    74

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

  2. #2
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

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

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  3. #3
    Join Date
    Jun 2008
    Location
    Netherlands
    Posts
    106

    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.

  4. #4
    Join Date
    Jun 2006
    Posts
    437

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

  5. #5
    Join Date
    Jan 2005
    Posts
    74

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

  6. #6
    Join Date
    Jun 2004
    Location
    Kashmir, India
    Posts
    6,808

    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_idfrom sys.objects where type 'U' 

  7. #7
    Join Date
    Jun 2006
    Posts
    437

    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

  8. #8
    Join Date
    Jun 2008
    Posts
    29

    Re: How to Delete All tables' records?

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

    TRUNCATE TABLE @TableName

  9. #9
    Join Date
    Jan 2005
    Posts
    74

    Re: How to Delete All tables' records?

    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?

  10. #10
    Join Date
    Jun 2006
    Posts
    437

    Re: How to Delete All tables' records?

    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.

    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.

  11. #11
    Join Date
    Jan 2005
    Posts
    74

    Re: How to Delete All tables' records?

    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_idfrom 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_idfrom 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!

  12. #12
    Join Date
    Feb 2008
    Location
    Bangalore
    Posts
    149

    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

  13. #13
    Join Date
    Jul 2004
    Location
    Chennai, India
    Posts
    1,064

    Re: How to Delete All tables' records?

    Madhivanan

    Failing to plan is Planning to fail

  14. #14
    Join Date
    Jan 2005
    Posts
    74

    Re: How to Delete All tables' records?

    Hi, thanks all. I achieve more Knowledge.

  15. #15
    Join Date
    Jan 2005
    Posts
    74

    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
  •  





Click Here to Expand Forum to Full Width

Featured