使用 T-SQL 截斷數據庫中的所有表

有沒有辦法在不使用 TSQL 語言中的 sp_MSForEachTable 的情況下截斷數據庫中的所有表？背景：不是我的電話，DBA 公司政策不允許 sp_MSForEachTable。

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'

想知道這是否是最有效的方式，還是其他可用的選擇？

select 'truncate table ' + Table_Schema + '.' + Table_Name from INFORMATION_SCHEMA.tables where table_type = 'base table'

另一個站點推薦這個： https ://www.mssqltips.com/sqlservertip/3218/truncate-all-tables-in-a-sql-server-database/

/* TRUNCATE ALL TABLES IN A DATABASE */
DECLARE @dropAndCreateConstraintsTable TABLE
       (
        DropStmt VARCHAR(MAX)
       ,CreateStmt VARCHAR(MAX)
       )
/* Gather information to drop and then recreate the current foreign key constraints  */
INSERT  @dropAndCreateConstraintsTable
       SELECT  DropStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema
               + '].[' + ForeignKeys.ForeignTableName + '] DROP CONSTRAINT ['
               + ForeignKeys.ForeignKeyName + ']; '
              ,CreateStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema
               + '].[' + ForeignKeys.ForeignTableName
               + '] WITH CHECK ADD CONSTRAINT [' + ForeignKeys.ForeignKeyName
               + '] FOREIGN KEY([' + ForeignKeys.ForeignTableColumn
               + ']) REFERENCES [' + SCHEMA_NAME(sys.objects.schema_id)
               + '].[' + sys.objects.[name] + ']([' + sys.columns.[name]
               + ']); '
       FROM    sys.objects
       INNER JOIN sys.columns
               ON ( sys.columns.[object_id] = sys.objects.[object_id] )
       INNER JOIN ( SELECT sys.foreign_keys.[name] AS ForeignKeyName
                          ,SCHEMA_NAME(sys.objects.schema_id) AS ForeignTableSchema
                          ,sys.objects.[name] AS ForeignTableName
                          ,sys.columns.[name] AS ForeignTableColumn
                          ,sys.foreign_keys.referenced_object_id AS referenced_object_id
                          ,sys.foreign_key_columns.referenced_column_id AS referenced_column_id
                    FROM   sys.foreign_keys
                    INNER JOIN sys.foreign_key_columns
                           ON ( sys.foreign_key_columns.constraint_object_id = sys.foreign_keys.[object_id] )
                    INNER JOIN sys.objects
                           ON ( sys.objects.[object_id] = sys.foreign_keys.parent_object_id )
                    INNER JOIN sys.columns
                           ON ( sys.columns.[object_id] = sys.objects.[object_id] )
                              AND ( sys.columns.column_id = sys.foreign_key_columns.parent_column_id )
                  ) ForeignKeys
               ON ( ForeignKeys.referenced_object_id = sys.objects.[object_id] )
                  AND ( ForeignKeys.referenced_column_id = sys.columns.column_id )
       WHERE   ( sys.objects.[type] = 'U' )
               AND ( sys.objects.[name] NOT IN ( 'sysdiagrams' ) )
/* SELECT * FROM @dropAndCreateConstraintsTable AS DACCT  --Test statement*/
DECLARE @DropStatement NVARCHAR(MAX)
DECLARE @RecreateStatement NVARCHAR(MAX)
/* Drop Constraints */
DECLARE Cur1 CURSOR READ_ONLY
FOR
       SELECT  DropStmt
       FROM    @dropAndCreateConstraintsTable
OPEN Cur1
FETCH NEXT FROM Cur1 INTO @DropStatement
WHILE @@FETCH_STATUS = 0
     BEGIN
           PRINT 'Executing ' + @DropStatement
           EXECUTE sp_executesql @DropStatement
           FETCH NEXT FROM Cur1 INTO @DropStatement
     END
CLOSE Cur1
DEALLOCATE Cur1
/* Truncate all tables in the database in the dbo schema */
DECLARE @DeleteTableStatement NVARCHAR(MAX)
DECLARE Cur2 CURSOR READ_ONLY
FOR
       SELECT  'TRUNCATE TABLE [dbo].[' + TABLE_NAME + ']'
       FROM    INFORMATION_SCHEMA.TABLES
       WHERE   TABLE_SCHEMA = 'dbo'
               AND TABLE_TYPE = 'BASE TABLE'
 /* Change your schema appropriately if you don't want to use dbo */
OPEN Cur2
FETCH NEXT FROM Cur2 INTO @DeleteTableStatement
WHILE @@FETCH_STATUS = 0
     BEGIN
           PRINT 'Executing ' + @DeleteTableStatement
           EXECUTE sp_executesql @DeleteTableStatement
           FETCH NEXT FROM Cur2 INTO @DeleteTableStatement
     END
CLOSE Cur2
DEALLOCATE Cur2
/* Recreate foreign key constraints  */
DECLARE Cur3 CURSOR READ_ONLY
FOR
       SELECT  CreateStmt
       FROM    @dropAndCreateConstraintsTable
OPEN Cur3
FETCH NEXT FROM Cur3 INTO @RecreateStatement
WHILE @@FETCH_STATUS = 0
     BEGIN
           PRINT 'Executing ' + @RecreateStatement
           EXECUTE sp_executesql @RecreateStatement
           FETCH NEXT FROM Cur3 INTO @RecreateStatement
     END
CLOSE Cur3
DEALLOCATE Cur3
GO   

這個答案使用，所以不能利用：sp_MSForEachTable

https://stackoverflow.com/questions/155246/how-do-you-truncate-all-tables-in-a-database-using-tsql

我不確定為什麼要截斷數據庫中的所有表，但是有幾種方法可以做到這一點。您可以使用游標循環 sys.tables 或 INFORMATION_SCHEMA.TABLES：

DECLARE @SqlCmd NVARCHAR(MAX)

DECLARE C1 CURSOR FOR
   SELECT N'TRUNCATE TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ';'
   FROM sys.tables t
   INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
   WHERE t.[name] <> 'sysdiagrams' 
       AND t.is_ms_shipped = 0

OPEN C1

FETCH NEXT FROM C1 INTO @SqlCmd

WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT @SqlCmd
   -- EXEC sp_executesql @SqlCmd

   FETCH NEXT FROM C1 INTO @SqlCmd
END

CLOSE C1
DEALLOCATE C1

或者您可以使用 COALESCE 方法來生成組合字元串：

DECLARE @SqlCmd NVARCHAR(MAX)

SELECT @SqlCmd = COALESCE(@SqlCmd + CHAR(10), '') + N'TRUNCATE TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ';'
FROM sys.tables t
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE t.[name] <> 'sysdiagrams' 
   AND t.is_ms_shipped = 0

PRINT @SqlCmd

在這些範例中，您可以複製列印的語句並執行它們，也可以使用sp_executesql直接執行命令。

正如George Palacios所強調的，此解決方案不處理外鍵。這裡有一個生成命令以刪除和重新創建外鍵的解決方案，它可以很好地工作。

引用自：https://dba.stackexchange.com/questions/233202