[SQL]好用語法:一鍵清空資料並保留資料表名稱

 目前有一個測試用資料庫實際檔案太大，但需要保留DB名稱在裡面卡位，也想保留相關TABLE

所以決定把內容一次清除就好

可以看到檔案原本有7G

下了指令之後原本檔案大小還是沒有改變，最後在DB 屬性修改了Initial Size後變成功縮小

實際做法如下:

步驟1. 下一鍵清除的SQL指令

-- 取得資料庫中的資料表，並建立儲存到 #LISTTABLES 這資料表中
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

步驟2.手動在DB右鍵的屬性Initial Size 輸入1 MB

參考資料:

ShunNien's Blog

https://shunnien.github.io/2017/05/02/delete-all-table-data/

The Will Will Web

https://blog.miniasp.com/post/2007/11/16/SQL-Tips-Delete-All-Data-in-whole-Database