Empty all table or delete all data from all table by sp_MSforeachtable in SQL-SERVER 2005

Empty all table or delete all data from all table by sp_MSforeachtable in SQL-SERVER 2005

As a developer, many times we need to delete all the records from all tables. For example we are developing one application in our development server. At the time of developing we must have entered lots of temporary data in our database; we shouldn’t upload all those data along with structure at live server. We should have to delete all dummy and temp data.

Note: This article is moved to following URL on 6th NOV 2012. Kindly use following link:

http://blog.extreme-advice.com/2009/03/04/empty-all-table-or-delete-all-data-from-all-table-by-sp_msforeachtable-in-sql-server-2005/

Advertisements

22 Responses to “Empty all table or delete all data from all table by sp_MSforeachtable in SQL-SERVER 2005”

  1. soundar Says:

    it helps me lot…thx a lot.

  2. Naveed Abbas Says:

    thats cool……
    but i want to know how to copy a table record to another table and how to delete
    all record from a single table????????????
    please reply quickly i am working on a project

    • riteshshah Says:

      Insert Into Table2 (field1,field2)
      Select Field1,Field2 From Table2 WHERE YourConditionGoesHere
      GO

      above script will copy record from Table1 to Table2 than you can use standar DELETE statement to delelte those records from Table1 or from wherever you want.

  3. Palla Says:

    I used the trigger written by u.. but it’s showing syntax error near ” For Create_table,Create_procedure”

    Pls help me.. below is my requirement

    I want to get all the history of database user activity with his operational sql command… database and all..

  4. diwakar Says:

    can u show me with example use truncate table in sql server 2008?

    • riteshshah Says:

      Hi Diwakar,

      I have used “DELETE” statement, you can use “Truncate” there. if it is not working, kindly show me your code. just replace

      EXEC sp_MSForEachTable ‘DELETE FROM ?’

      with following line of code

      EXEC sp_MSForEachTable ‘TRUNCATE TABLE ?’

  5. Randeep Chauhan Says:

    Dear sir ,
    your code fine work in case of empty all data from database.but myquestion is that suppose there are 10 tables in our database.for example i dont want to empty some table like tbtest1,tbtest2 .
    how it solve it.
    Thaks again sir;
    Randeep Chauhan.

    • riteshshah Says:

      well in this case the method saw in this article won’t be useful, you have to go to the script given in Bihag’s article, whose link is already there in above article. you can set condition for those table you don’t want to delete in the query “Information_Schema.tables” in Bihag’s article.

      • Randeep Chauhan Says:

        Thaks sir ,i follow Bihag’s article.I get some success.

        Create Procedure dbo.sp_EmptyAllTables (@ResetIdentity Bit)

        As
        Begin
        Declare @SQL VarChar(500)
        Declare @TableName VarChar(255)
        Declare @ConstraintName VarChar(500)
        Declare curAllForeignKeys SCROLL CurSor For Select Table_Name,Constraint_Name From Information_Schema.Table_Constraints Where Constraint_Type=’FOREIGN KEY’
        Open curAllForeignKeys
        Fetch Next From curAllForeignKeys INTO @TableName,@ConstraintName
        While @@FETCH_STATUS=0
        Begin
        Set @SQL = ‘ALTER TABLE ‘ + @TableName + ‘ NOCHECK CONSTRAINT ‘ + @ConstraintName
        Execute(@SQL)
        Fetch Next From curAllForeignKeys INTO @TableName,@ConstraintName
        End
        Declare curAllTables Cursor For Select Table_Name From Information_Schema.Tables Where TABLE_TYPE=’BASE TABLE’
        Open curAllTables
        Fetch Next From curAllTables INTO @TableName
        While @@FETCH_STATUS=0
        Begin
        if @TableName!=’tbtest1′ and @TableName!=’tbtest2′ ///where i put condition
        Set @SQL = ‘DELETE FROM ‘ + @TableName
        If @ResetIdentity = 1 AND OBJECTPROPERTY (OBJECT_ID(@TableName),’TableHasIdentity’)=1
        Set @SQL = @SQL + ‘; DBCC CHECKIDENT(”’ + @TableName + ”’,RESEED,0)’
        Execute(@SQL)
        Fetch Next From curAllTables INTO @TableName
        End
        Fetch First From curAllForeignKeys INTO @TableName,@ConstraintName
        While @@FETCH_STATUS=0
        Begin
        Set @SQL = ‘ALTER TABLE ‘ + @TableName + ‘ CHECK CONSTRAINT ‘ + @ConstraintName
        Execute(@SQL)
        Fetch Next From curAllForeignKeys INTO @TableName,@ConstraintName
        End
        Close curAllTables
        Deallocate curAllTables
        Close curAllForeignKeys
        Deallocate curAllForeignKeys
        End

    • Saravanan Seshan Says:

      Use this following query:

      EXEC sp_msforeachtable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’

      exec sp_MSforeachtable
      @command1 = ‘delete from ?’,
      @whereand = ‘and o.name not in( ”ABC”,”XYZ”)’

      EXEC sp_msforeachtable ‘ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL’

    • Saravanan Seshan Says:

      Use this following query:

      EXEC sp_msforeachtable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’

      exec sp_MSforeachtable
      @command1 = ‘delete from ?’,
      @whereand = ‘and o.name not in( ”tbtest1”,”tbtest2”)’

      EXEC sp_msforeachtable ‘ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL’

  6. Jaydeep Solanki Says:

    How to delete data from table if PK and FK is available

  7. Daniel Veiga Says:

    Great work :). I just have a problem.
    I have a lot of databases in a SQL SERVER 2005 and I only want clear one database.
    My problem is that I’m afraid that this procedure will clear all databases in the company. If that happen probably I will be fired 🙂 …

    What measure can I take to be sure that will not happen?

    Thanks

  8. Hamed Says:

    Thanks a lot.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: