我需要TRUNCATE或DELETE架構中的所有表。
我找到了這段代碼：
-- disable all constraints
EXEC sp_MSForEachTable @command1='ALTER TABLE ? NOCHECK CONSTRAINT all',@whereand='and Schema_Id=Schema_id(''Person'')'
-- delete data in all tables
Exec Sp_msforeachtable @command1='Truncate Table ?',@whereand='and Schema_Id=Schema_id(''Person'')'
-- enable all constraints
exec sp_MSForEachTable @command1='ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all',@whereand='and Schema_Id=Schema_id(''Person'')'
-- if some of the tables have identity columns we may want to reseed them
EXEC sp_MSForEachTable @command1='DBCC CHECKIDENT ( ''?'', RESEED, 0)',@whereand='and Schema_Id=Schema_id(''Person'')'
但在 AdventureWorks 上，它給了我：
Cannot truncate table 'Person.Address' because it is being referenced by a FOREIGN KEY constraint.
所以我找到了這個替代代碼：
DECLARE @STRSQL NVARCHAR(MAX);
DECLARE @TABLE NVARCHAR(128);
DECLARE @SCHEMA_NAME VARCHAR(50)
SET @SCHEMA_NAME = 'Person'
SET @STRSQL = '';
DECLARE @C1 CURSOR SET @C1 = CURSOR
FOR
SELECT TOP 2 TABLE_SCHEMA '.' TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = @SCHEMA_NAME
OPEN @C1
FETCH NEXT
FROM @C1
INTO @TABLE
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @TABLE
SET @STRSQL = @STRSQL 'DELETE FROM ' @TABLE ';'
FETCH NEXT
FROM @C1
INTO @TABLE
END
CLOSE @C1
DEALLOCATE @C1
PRINT @STRSQL
EXEC sp_executesql @STRSQL
但結果是一樣的：
Person.Address
Person.AddressType
DELETE FROM Person.Address;DELETE FROM Person.AddressType;
Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_BusinessEntityAddress_Address_AddressID". The conflict occurred in database "AdventureWorks2014", table "Person.BusinessEntityAddress", column 'AddressID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_BusinessEntityAddress_AddressType_AddressTypeID". The conflict occurred in database "AdventureWorks2014", table "Person.BusinessEntityAddress", column 'AddressTypeID'.
The statement has been terminated.
如何TRUNCATE或DELETE架構中的所有表？
uj5u.com熱心網友回復：
您只需要在開頭用“洗掉所有外鍵”腳本包裝“從所有表中洗掉”腳本，最后用“重新創建所有外鍵”腳本。我在這里展示了一種方法：
- 在 SQL Server 中洗掉并重新創建所有外鍵約束
但是，我認為只撰寫資料庫腳本并從源代碼控制中清空物件比花費所有時間和精力一次從一個表中洗掉資料要干凈得多。
無論如何嘗試你正在做的事情（如果你截斷你也不需要檢查身份，我不確定我是否會使用無證和不受支持的程式，比如sp_msforeachtable，而且我也遠離INFORMATION_SCHEMA）。請在測驗資料庫上試試這個。
CREATE TABLE #x -- feel free to use a permanent table
(
drop_script NVARCHAR(MAX),
create_script NVARCHAR(MAX)
);
DECLARE @drop NVARCHAR(MAX) = N'',
@create NVARCHAR(MAX) = N'';
-- drop is easy, just build a simple concatenated list from sys.foreign_keys:
SELECT @drop = N'
ALTER TABLE ' QUOTENAME(cs.name) '.' QUOTENAME(ct.name)
' DROP CONSTRAINT ' QUOTENAME(fk.name) ';'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS ct
ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs
ON ct.[schema_id] = cs.[schema_id];
INSERT #x(drop_script) SELECT @drop;
-- create is a little more complex. We need to generate the list of
-- columns on both sides of the constraint, even though in most cases
-- there is only one column.
SELECT @create = N'
ALTER TABLE '
QUOTENAME(cs.name) '.' QUOTENAME(ct.name)
' ADD CONSTRAINT ' QUOTENAME(fk.name)
' FOREIGN KEY (' STUFF((SELECT ',' QUOTENAME(c.name)
-- get all the columns in the constraint table
FROM sys.columns AS c
INNER JOIN sys.foreign_key_columns AS fkc
ON fkc.parent_column_id = c.column_id
AND fkc.parent_object_id = c.[object_id]
WHERE fkc.constraint_object_id = fk.[object_id]
ORDER BY fkc.constraint_column_id
FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')
') REFERENCES ' QUOTENAME(rs.name) '.' QUOTENAME(rt.name)
'(' STUFF((SELECT ',' QUOTENAME(c.name)
-- get all the referenced columns
FROM sys.columns AS c
INNER JOIN sys.foreign_key_columns AS fkc
ON fkc.referenced_column_id = c.column_id
AND fkc.referenced_object_id = c.[object_id]
WHERE fkc.constraint_object_id = fk.[object_id]
ORDER BY fkc.constraint_column_id
FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') ');'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS rt -- referenced table
ON fk.referenced_object_id = rt.[object_id]
INNER JOIN sys.schemas AS rs
ON rt.[schema_id] = rs.[schema_id]
INNER JOIN sys.tables AS ct -- constraint table
ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs
ON ct.[schema_id] = cs.[schema_id]
WHERE rt.is_ms_shipped = 0 AND ct.is_ms_shipped = 0;
UPDATE #x SET create_script = @create;
PRINT @drop;
PRINT @create;
EXEC sys.sp_executesql @drop
-- clear out data etc. here
DECLARE @truncate nvarchar(max) = N'';
SELECT @truncate = N'TRUNCATE TABLE ' QUOTENAME(s.name)
N'.' QUOTENAME(t.name) N';'
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.[schema_id] = t.[schema_id];
EXEC sys.sp_executesql @truncate;
EXEC sys.sp_executesql @create;
筆記：
- 這是未經測驗的。盡我所能：在測驗資料庫上試試這個。
- 這意味著只執行一次，所以我不會洗掉 #temp 表（如果事情變南，讓它保持足夠長的時間以排除故障可能很有用）
PRINT不一定會向您顯示將要執行的完整命令，因此它不是確定腳本是否正確的有效方法。它只是作為一個快速的眼球。如果您真的想查看整個命令，則需要更詳細的內容。- 這不處理索引視圖，我敢肯定還有其他限制可能會阻止您截斷某些表（我在想時間或總是用飛地或記憶體加密），但我會單獨解決這些并保留通過使用更密集的日志洗掉來截斷而不是“修復”它。
uj5u.com熱心網友回復：
謝謝@AaronBertrand，我分叉了你很棒的查詢，以便為我需要做的事情制作一個更合適的查詢：
SELECT cs.name AS SchemaName
,ct.name AS TableName
,rt.name AS ColumnName
,fk.name AS ForeignKeyName
,fk.object_id AS ObjectID
,fk.parent_object_id AS ParentObjectID
,
-- drop constraint
N'
ALTER TABLE ' QUOTENAME(cs.name) '.' QUOTENAME(ct.name) ' DROP CONSTRAINT ' QUOTENAME(fk.name) ';' AS Drop_Constraint_Script
,
-- create constraint
N'
ALTER TABLE ' QUOTENAME(cs.name) '.' QUOTENAME(ct.name) ' ADD CONSTRAINT ' QUOTENAME(fk.name) ' FOREIGN KEY (' STUFF((
SELECT ',' QUOTENAME(c.name)
-- get all the columns in the constraint table
FROM sys.columns AS c
INNER JOIN sys.foreign_key_columns AS fkc ON fkc.parent_column_id = c.column_id
AND fkc.parent_object_id = c.[object_id]
WHERE fkc.constraint_object_id = fk.[object_id]
ORDER BY fkc.constraint_column_id
FOR XML PATH(N'')
,TYPE
).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') ') REFERENCES ' QUOTENAME(rs.name) '.' QUOTENAME(rt.name) '(' STUFF((
SELECT ',' QUOTENAME(c.name)
-- get all the referenced columns
FROM sys.columns AS c
INNER JOIN sys.foreign_key_columns AS fkc ON fkc.referenced_column_id = c.column_id
AND fkc.referenced_object_id = c.[object_id]
WHERE fkc.constraint_object_id = fk.[object_id]
ORDER BY fkc.constraint_column_id
FOR XML PATH(N'')
,TYPE
).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') ');' AS Create_Constraint_Script
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS rt -- referenced table
ON fk.referenced_object_id = rt.[object_id]
INNER JOIN sys.schemas AS rs ON rt.[schema_id] = rs.[schema_id]
INNER JOIN sys.tables AS ct -- constraint table
ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs ON ct.[schema_id] = cs.[schema_id]
WHERE rt.is_ms_shipped = 0
AND ct.is_ms_shipped = 0
ORDER BY 1
,2
,3
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/404963.html
標籤：
上一篇：提取字符之間的資料SQL