我想洗掉參考 SQL Server 中用戶表的所有表的所有行
用戶表名稱：security.User
用戶表 PK：UserId
我對表格的查詢
Delete From FKschema.FKTable where FKColumn not in (1,2)
如何對所有表進行一次查詢？
uj5u.com熱心網友回復：
您應該使用 CURSOR 并創建字串并執行它
DECLARE @tableName NVARCHAR(100), @schema NVARCHAR(50), @refcolumn NVARCHAR(50)
DECLARE @Query NVARCHAR(max)= ' DELETE from %s.%s where %s not in (1,2)'
DECLARE Cur CURSOR FOR
SELECT object_name(fk.parent_object_id) as Table_Name,SCHEMA_NAME(schema_id),(SELECT name FROM sys.columns c WHERE c.column_id=fk_cols.parent_column_id AND c.object_id=fk.parent_object_id) AS columnName
FROM sys.foreign_keys fk
inner join sys.foreign_key_columns fk_cols
on fk_cols.constraint_object_id = fk.object_id
WHERE fk.referenced_object_id = object_id('security.users','U');
OPEN Cur
FETCH NEXT FROM Cur INTO @tableName, @schema,@refcolumn
WHILE @@FETCH_STATUS=0
BEGIN
DECLARE @text NVARCHAR(max)=FORMATMESSAGE(@Query, @schema,@tableName,@refcolumn)
EXECUTE(@text)
FETCH NEXT FROM Cur INTO @tableName, @schema,@refcolumn
PRINT @text
END
CLOSE Cur
DEALLOCATE Cur
uj5u.com熱心網友回復：
當我們為表設定外鍵時，我們還提供了另一個屬性（在 django 中是 on_delete=models.CASCADE），這意味著如果洗掉父級，也會洗掉關系。
例如，
CREATE TABLE inventory
( inventory_id INT PRIMARY KEY,
CONSTRAINT fk_inv_product_id
FOREIGN KEY (product_id)
REFERENCES products (product_id)
ON DELETE CASCADE // this is what you need
);
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/422537.html
標籤：