CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 15 of 15

Hybrid View

  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' 

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