刪除數據庫所有table的資料（一）

最新推荐文章于 2024-08-29 10:59:52 发布

MasterLonely

最新推荐文章于 2024-08-29 10:59:52 发布

阅读量1.1k

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL Server 文章标签： table delete go

本文链接：https://blog.youkuaiyun.com/MasterLonely/article/details/7195825

SQL Server 专栏收录该内容

31 篇文章

订阅专栏

本文介绍如何在SQL环境中禁用和启用数据库对象的状态，包括禁止或允许外键、触发器、表数据操作和索引重建等关键操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--禁所有外鍵的語句
select  'ALTER TABLE ['  + b.name +  '] NOCHECK CONSTRAINT [' +  a.name +'];'  
from  sysobjects  a ,sysobjects  b    
where  a.xtype ='f' and  a.parent_obj = b.id
--or
select  'ALTER TABLE ['  + name +  '] NOCHECK CONSTRAINT  all ' 
from  sysobjects  a 
where  a.xtype ='u' 

--禁所有觸發器的語句
select  'ALTER TABLE ['  + name +  '] DISABLE  TRIGGER  all ' 
from  sysobjects  a 
where  a.xtype ='u' 




--刪除所有表資料
/*
select  'TRUNCATE TABLE   ['  + a.name +  '] '  
from  sysobjects  a 
where  a.xtype ='u'
*/ 
--因為要加 GO，在一個語句裡解決不了。
declare @name varchar(100)
declare cur cursor  for
	select name from sysobjects where xtype='u' order by name
open cur
fetch next from cur into @name
while @@fetch_status=0
begin
	
	print 'TRUNCATE TABLE   ['  +@name +  '] '  
	print 'GO'

	fetch next from cur into @name
end
close cur
deallocate cur


/*
select  'DELETE   ['  + a.name +  '] '
from  sysobjects  a 
where  a.xtype ='u' 
*/
--還是加Go的更好用。中間一個出錯了也沒有關系
declare @name varchar(100)
declare cur cursor  for
	select name from sysobjects where xtype='u' order by name
open cur
fetch next from cur into @name
while @@fetch_status=0
begin
	
	print 'DELETE    ['  +@name +  '] '  
	print 'GO'

	fetch next from cur into @name
end
close cur
deallocate cur



--開啟所有外鍵的語句
select  'ALTER TABLE [' + b.name +  '] CHECK CONSTRAINT [' +  a.name +'];'  
from  sysobjects  a ,sysobjects  b    
where  a.xtype ='f' and  a.parent_obj = b.id
or
select  'ALTER TABLE ['  + name +  '] CHECK CONSTRAINT  all ' 
from  sysobjects  a 
where  a.xtype ='u' 

----開啟所有觸發器的語句
select  'ALTER TABLE ['  + name +  '] enable  TRIGGER  all ' 
from  sysobjects  a 
where  a.xtype ='u' 


--所有 identity表復原為1的語句
select  'dbcc checkident(['+name+'],reseed ,0) '
from  sysobjects  a 
where  a.xtype ='u'  and objectproperty(id,'TableHasIdentity')=1


--重建所有索引

select  'dbcc DBREINDEX(['+name+']) '
from  sysobjects  a 
where  a.xtype ='u'