Emptying data for all tables in a database using SQL statements

Recently found that the database is too large, the space is not enough, so it is intended to complete the database of data cleanup, but the table is very many, one by one empty, it is really troublesome, so you want to use SQL statements to clear all the data at once. Three methods were found to clear. The database used is MS SQL SERVER.
1. Search for all table names, constructed as an SQL statement

650) this.width=650; "src=" Http://image57.360doc.com/DownloadImg/2012/12/1910/29021121_1 "align=" Top "/>Declare @trun_name varchar(8000)
650) this.width=650; "src=" Http://image57.360doc.com/DownloadImg/2012/12/1910/29021121_1 "align=" Top "/>Set @trun_name="'
650) this.width=650; "src=" Http://image57.360doc.com/DownloadImg/2012/12/1910/29021121_1 "align=" Top "/>Select @trun_name=@trun_name + 'TRUNCATE TABLE' + [name] + ' ' fromsysobjectswherextype='U' andStatus> 0
650) this.width=650; "src=" Http://image57.360doc.com/DownloadImg/2012/12/1910/29021121_1 "align=" Top "/>exec (@trun_name)

This method is suitable for tables where there are not very many cases, otherwise the number of tables is too large to exceed the length of the string and cannot be completely cleaned up.
2. Clean all tables with cursors

650) this.width=650; "src=" Http://image57.360doc.com/DownloadImg/2012/12/1910/29021121_1 "align=" Top "/>Declare @trun_name varchar( -)
650) this.width=650; "src=" Http://image57.360doc.com/DownloadImg/2012/12/1910/29021121_1 "align=" Top "/>DeclareName_cursorcursor for
650) this.width=650; "src=" Http://image57.360doc.com/DownloadImg/2012/12/1910/29021121_1 "align=" Top "/>Select 'TRUNCATE TABLE' +name fromsysobjectswherextype='U' andStatus> 0
650) this.width=650; "src=" Http://image57.360doc.com/DownloadImg/2012/12/1910/29021121_1 "align=" Top "/>OpenName_cursor
650) this.width=650; "src=" Http://image57.360doc.com/DownloadImg/2012/12/1910/29021121_1 "align=" Top "/>Fetch Next fromName_cursor into @trun_name
650) this.width=650; "src=" Http://image57.360doc.com/DownloadImg/2012/12/1910/29021121_1 "align=" Top "/> while @ @FETCH_STATUS = 0
650) this.width=650; "src=" Http://image57.360doc.com/DownloadImg/2012/12/1910/29021121_1 "align=" Top "/>begin
650) this.width=650; "src=" Http://image57.360doc.com/DownloadImg/2012/12/1910/29021121_1 "align=" Top "/>exec (@trun_name)
650) this.width=650; "src=" Http://image57.360doc.com/DownloadImg/2012/12/1910/29021121_1 "align=" Top "/>Print 'truncated table' + @trun_name
650) this.width=650; "src=" Http://image57.360doc.com/DownloadImg/2012/12/1910/29021121_1 "align=" Top "/>Fetch Next fromName_cursor into @trun_name
650) this.width=650; "src=" Http://image57.360doc.com/DownloadImg/2012/12/1910/29021121_1 "align=" Top "/>End
650) this.width=650; "src=" Http://image57.360doc.com/DownloadImg/2012/12/1910/29021121_1 "align=" Top "/>CloseName_cursor
650) this.width=650; "src=" Http://image57.360doc.com/DownloadImg/2012/12/1910/29021121_1 "align=" Top "/>deallocateName_cursor
650) this.width=650; "src=" Http://image57.360doc.com/DownloadImg/2012/12/1910/29021121_1 "align=" Top "/>

This is my own construction, can be called as a stored procedure, able to empty all the table data at once, and can also be selected to clear the table.
3. Leveraging Microsoft's undisclosed stored procedures

650) this.width=650; "src=" Http://image57.360doc.com/DownloadImg/2012/12/1910/29021121_1 "align=" Top "/> EXEC sp_msforeachtable "truncate table ?"

This method can empty all tables at once, but cannot add filters.

This article is from "Ghost" blog, please make sure to keep this source http://caizi.blog.51cto.com/5234706/1565875

Emptying data for all tables in a database using SQL statements