从数据库的所有表中删除所有行
某人必须已经编写了脚本才能从数据库的所有表中删除所有行。 使用DELETE命令不是一个选项,因为它可能会在大型表上花费时间。 当然,在stackoverflow和其他地方有很多例子,但是它们不适用于使用外键的表。从数据库的所有表中删除所有行
基本上,脚本应该这样做:
- 存储所有外键定义在临时表
- 删除所有外键
- 截断所有表
- 恢复外键
我想我有:
IF OBJECT_ID('tempdb..#ForeignKeys') IS NOT NULL
DROP TABLE #ForeignKeys;
WITH ForeignKeys AS (
SELECT
QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) AS ParentTable
, QUOTENAME(SCHEMA_NAME(rt.schema_id)) + '.' + QUOTENAME(rt.name) AS ReferenceTable
, QUOTENAME(f.name) AS ConstraintName
, STUFF(Parent.Cols, 1, 1, '') AS ParentColumns
, STUFF(Reference.Cols, 1, 1, '') AS ReferenceColumns
, REPLACE(f.update_referential_action_desc, '_', ' ') AS UpdateAction
, REPLACE(f.delete_referential_action_desc, '_', ' ') AS DeleteAction
FROM
sys.tables AS t
LEFT JOIN sys.foreign_keys AS f
ON f.parent_object_id = t.object_id
AND f.type = 'F'
LEFT JOIN sys.tables AS rt
ON f.referenced_object_id = rt.object_id
CROSS APPLY
(
SELECT
',' + QUOTENAME(COL_NAME(fc.parent_object_id, fc.parent_column_id))AS [text()]
FROM
sys.foreign_key_columns AS fc
WHERE
fc.constraint_object_id = f.object_id
ORDER BY
fc.constraint_column_id
FOR XML PATH('')
) Parent(Cols)
CROSS APPLY
(
SELECT
',' + QUOTENAME(COL_NAME(fc.referenced_object_id, fc.referenced_column_id)) AS [text()]
FROM
sys.foreign_key_columns AS fc
WHERE
fc.constraint_object_id = f.object_id
ORDER BY
fc.constraint_column_id
FOR XML PATH('')
) Reference(Cols)
)
SELECT
ParentTable AS TableName
, 'ALTER TABLE ' + ParentTable + ' DROP CONSTRAINT ' + ConstraintName AS DropCmd
, 'TRUNCATE TABLE ' + ParentTable AS TruncateCmd
, 'ALTER TABLE ' + ParentTable + ' ADD CONSTRAINT ' + ConstraintName + ' FOREIGN KEY('
+ ParentColumns + ') REFERENCES ' + ReferenceTable + ' (' + ReferenceColumns
+ ') ON UPDATE ' + UpdateAction
+ ' ON DELETE ' + DeleteAction COLLATE SQL_Latin1_General_CP1_CI_AS AS CreateCmd
INTO
#ForeignKeys
FROM
ForeignKeys
ORDER BY
1;
-- SELECT * FROM #ForeignKeys
DECLARE @TableName SYSNAME
DECLARE @Sql NVARCHAR(MAX)
-- Drop all constraints
DECLARE FkCursor CURSOR FOR
SELECT
TableName
, DropCmd
FROM
#ForeignKeys
WHERE
DropCmd IS NOT NULL
OPEN FkCursor
FETCH NEXT FROM FkCursor INTO @TableName, @Sql
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @TableName + ' : ' + @sql
EXEC sp_executesql @Sql
FETCH NEXT FROM FkCursor INTO @TableName, @Sql
END
CLOSE FkCursor
DEALLOCATE FkCursor
-- Truncate all tables
DECLARE FkCursor CURSOR FOR
SELECT
TableName
, TruncateCmd
FROM
#ForeignKeys
OPEN FkCursor
FETCH NEXT FROM FkCursor INTO @TableName, @Sql
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @TableName + ' : ' + @sql
EXEC sp_executesql @Sql
FETCH NEXT FROM FkCursor INTO @TableName, @Sql
END
CLOSE FkCursor
DEALLOCATE FkCursor
-- Create all foreign keys
DECLARE FkCursor CURSOR FOR
SELECT
TableName
, CreateCmd
FROM
#ForeignKeys
WHERE
CreateCmd IS NOT NULL
OPEN FkCursor
FETCH NEXT FROM FkCursor INTO @TableName, @Sql
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @TableName + ' : ' + @sql
EXEC sp_executesql @Sql
FETCH NEXT FROM FkCursor INTO @TableName, @Sql
END
CLOSE FkCursor
DEALLOCATE FkCursor
DROP TABLE #ForeignKeys;
DECLARE @drop NVARCHAR(MAX), @truncate NVARCHAR(MAX), @create NVARCHAR(MAX);
SELECT @drop = N'', @truncate = N'', @create = N'';
;WITH x AS
(
SELECT id = f.[object_id],
cname = QUOTENAME(f.name),
ctable = QUOTENAME(OBJECT_SCHEMA_NAME(f.parent_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(f.parent_object_id)),
ccol = QUOTENAME(COL_NAME(fc.parent_object_id,fc.parent_column_id)),
rtable = QUOTENAME(OBJECT_SCHEMA_NAME(f.referenced_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(f.referenced_object_id)),
rcol = QUOTENAME(COL_NAME(fc.referenced_object_id,fc.referenced_column_id)),
ou = f.update_referential_action_desc COLLATE SQL_Latin1_General_CP1_CI_AS,
od = f.delete_referential_action_desc COLLATE SQL_Latin1_General_CP1_CI_AS
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.[object_id] = fc.constraint_object_id
-- where clause to leave out certain tables here
),
y AS
(
SELECT
d = CHAR(13) + CHAR(10) + 'ALTER TABLE ' + ctable + ' DROP CONSTRAINT ' + cname + ';',
c = CHAR(13) + CHAR(10) + 'ALTER TABLE ' + ctable + ' ADD CONSTRAINT ' + cname
+ ' FOREIGN KEY (' + STUFF((SELECT ',' + ccol FROM x AS x2
WHERE x2.id = x.id FOR XML PATH('')), 1, 1, '')
+ ') REFERENCES ' + rtable + '(' + STUFF((SELECT ',' + rcol FROM x AS x3
WHERE x3.id = x.id FOR XML PATH('')), 1, 1, '') + ')'
+ CASE WHEN od <> 'NO_ACTION' THEN
' ON DELETE ' + REPLACE(od, 'SET_', 'SET ') ELSE '' END
+ CASE WHEN ou <> 'NO_ACTION' THEN
' ON UPDATE ' + REPLACE(ou, 'SET_', 'SET ') ELSE '' END
FROM x
)
SELECT
@drop = @drop + d,
@create = @create + c
FROM y GROUP BY d,c;
SELECT @truncate = @truncate + CHAR(13) + CHAR(10) + 'TRUNCATE TABLE '
+ QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ';'
FROM sys.tables
-- where clause to leave out certain tables here
;
-- use results to text. Note that for 200 tables you won't be able to
-- manually inspect the entire script in Management Studio because it
-- will only show a certain number of characters depending on settings.
SELECT @drop;
SELECT @truncate;
SELECT @create;
-- when you are happy, uncomment these:
--EXEC sp_executesql @drop;
--EXEC sp_executesql @truncate;
--EXEC sp_executesql @create;
我想我已经找到了类似的解决方案,但是如果你有外键的开启(更新|删除设置(默认| NULL)'或者带空格的列,你将会遇到问题 – psadac 2012-04-17 17:45:41
当然,如果你用空格命名列。 :添加QUOTENAME(上面已经修正)现在如果你命名你的FK约束表TABLE?有各种各样的边缘情况,你可以花上几个小时来应对,但是你想要一个100%的防弹解决方案还是只是一个普遍的想法,你可以完美的,如果有几个非典型的东西遗漏,你已经清楚地知道? – 2012-04-17 17:49:56
列名空间不是人的选择,你可以想象。但是你是对的,这将是很难有一些“独白”,我用QUOTENAME击倒。 – psadac 2012-04-17 19:01:14
右键单击对象(在对象资源管理器中),然后单击脚本作为 - >拖放到。使用与 - > Create to相同的方法和脚本。这应该为您提供Dropping的脚本以及创建数据库中的所有对象。按照你喜欢的顺序运行它们。
或者如果你有很多的表和对象,你可以右键点击数据库 - >去任务,然后点击生成脚本。
哇,如果有500张桌子,这根本就不是很有成效,而外键意味着你将不得不做很多工作来确定正确的顺序。 – 2012-04-17 15:38:54
叶普。但是我们知道他是否有500张桌子? – rvphx 2012-04-17 15:39:27
即使有10个,这是很多手动指向和点击,并且很容易跳过表格。 – 2012-04-17 15:40:01
到目前为止你写了些什么? – rvphx 2012-04-17 15:32:43
如果您有所有表的创建脚本,为什么不删除所有表(或数据库),然后重新运行创建脚本? – 2012-04-17 15:33:17
删除对FK引用的表格不起作用。对于你没有被FK引用的表,然后转向。定义年龄。我有超过100 GB的数据库,我有一个脚本清除所有行使用截断和删除像20分钟。 – Paparazzi 2012-04-17 15:55:29