The Will Will Web

記載著 Will 在網路世界的學習心得與技術分享

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]

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