# SQL Tips: 刪除資料庫中所有資料表的資料

在開發一個新專案時，有時候為了寫一個轉資料的轉檔程式轉到很頭痛，因為轉到一半有時候程式會中斷，資料只有 INSERT 到一半而已，通常都要刪掉所有資料重新匯入一次，但資料表很多的時候就很累人了。

因此我寫了個 T-SQL 一次刪掉所有表格中的資料。

\[code:tsql\]  
select  
    distinct 表格名稱  
    into #LISTTABLES  
from  
    (  
    SELECT  
        Top 100 PERCENT  
        a.TABLE\_NAME as 表格名稱  
    FROM  
        INFORMATION\_SCHEMA.TABLES  a  
        LEFT JOIN INFORMATION\_SCHEMA.COLUMNS b ON ( a.TABLE\_NAME=b.TABLE\_NAME )  
    WHERE  
        a.TABLE\_TYPE='BASE TABLE'  
        and a.TABLE\_NAME != 'sysdiagrams'  
    ORDER BY  
        a.TABLE\_NAME, ordinal\_position) c;

declare  
    @tablename nvarchar(max)

declare icur cursor static for select \* from #LISTTABLES  
     
OPEN icur

fetch next from icur into @tablename  
while(@@FETCH\_STATUS=0)  
BEGIN

    -- 使用 TRUNCATE 最快，且會將表格中的自動編號欄位歸零  
    -- 但若表格有被參考 Foreign Key 的話，會無法使用 TRUNCATE 指令  
    exec ('TRUNCATE TABLE ' + @tablename);  

    -- 如果 TRUNCATE 不成功，還可以用 DELETE 刪除所有資料  
    exec ('delete from ' + @tablename + ' where 1=1');

    -- 使用 DELETE 不會將自動編號的欄位歸零，使用 DBCC CHECKIDENT 指令歸零  
    exec ('DBCC CHECKIDENT(''' + @tablename + ''', RESEED, 0)');

    FETCH NEXT FROM icur INTO @tablename  
END

close icur  
deallocate icur

drop table #LISTTABLES  
\[/code\]

注意：因為有些表格會有關連的限制，導致無法刪除資料，不過沒關係，多執行兩、三次就可以把資料刪光光了！