Shulou(Shulou.com)06/01 Report--

Clearing table data, preserving views, stored procedures, functions, and retaining table constraints, triggers, etc., can reserve a good shell for the failed database and import bad database data into this shell. Sometimes good results can be achieved in the recovery of databases such as user friends and Kingdee.

There are two ways to clear table data:

One method uses delete from [table name], which is slow for large databases and produces large log information, but for small libraries, the speed can be ignored.

The other is truncate table [table name], which is fast, but cannot clear table data with foreign keys.

When clearing table data, you can use a combination of two methods.

Disable all constraints when clearing table data, and enable constraints again when the cleanup is complete.

1. Use truncate table [table name] to clear table data and exclude tables with foreign key attributes. The clean statement is obtained as follows:

Use [name of the library to be operated]

Select

'alter table ['+ name+'] nocheck constraint all; alter table ['+ name+'] disable trigger all

Truncate table ['+ name+'];'

'alter table ['+ name +'] enable trigger all; alter table ['+ name +'] check constraint all

Go'

From sysobjects where id not in (select parent_object_id from sys.foreign_keys) and id not in (select referenced_object_id from sys.foreign_keys) and type='U'

The result of the above SQL statement is run again in the SQL query analyzer.

2. Clear the table data statement with delete from [Table name] to get the following

Use [name of the library to be operated]

SELECT 'alter table [' + object_name (id) +'] nocheck constraint all; alter table ['+ object_name (id) +'] disable trigger all

Delete from ['+ object_name (id) +'];'+'

Alter table ['+ object_name (id) +'] enable trigger all; alter table ['+ object_name (id) +'] check constraint all

Go'

TableName from sysobjects where type='U'

3, some MS SQL Server query analyzer, for the go after the above statement, the copy does not automatically wrap, you can save the running results to the text file, after the query analyzer is opened, it will automatically wrap. If you do not wrap automatically, the sql statement executes an error.

4. You may encounter some tables that cannot be cleared, check which tables are associated with foreign keys, disable the constraints of the two tables at the same time, and then use delete from [table name]. After that, the constraints of the two tables will be restored at the same time.

5. Check whether the data has been cleared successfully and thoroughly, and check the number of records with the following statement:

Use [name of the library to be operated]

SELECT object_name (i.id) TableName

Rows as RowCnt

FROM sysindexes i

INNER JOIN sysObjects o

ON (o.id = i.id AND o.xType ='U')

WHERE indid

0

ORDER BY RowCnt desc

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.