删除数据库中的数据.

1,删除数据库中的记录(在存储过程中的代码):

ALTER PROCEDURE USP_ClearAllCurrentDB
AS
    begin
    EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'    
    EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'    
    EXEC sp_MSForEachTable 'DELETE FROM ?'   
    EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'    
    EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'
 end

 2,删除某数据库中所有表(存储过程中):

SET NOCOUNT ON   
    --删除外键约束.   
    declare cur  cursor for     
    select 'alter table ['+ object_name(parent_obj) + ' ] drop constraint [' + name + '] ' from sysobjects where xtype = 'F'     
    open cur     
    declare @cur nvarchar(4000)     
    fetch next from cur into @cur     
    while @@fetch_status = 0       
        begin       
        exec(@cur)        
        fetch next from cur into @cur        
        end        
    close cur        
    deallocate cur        
    --删除表.     
    declare @T_Name nvarchar(4000)    
    set @T_Name = ''    
    select @T_Name = @T_Name +name +','  from sysobjects where xtype = 'U'     
    select @T_Name = 'drop table '+ left(@T_Name,len(@T_Name) - 1)    --这里@T_Name会以','结尾.    
    exec(@T_Name)

 

 

ref: http://stackoverflow.com/questions/3372213/how-to-delete-or-truncate-table-in-sql-server