当前位置：首页 -> AI编程基础 -> 数据库编程

SQL中的一些批量操作技巧(一)

2014-11-24 15:31:14 · 作者: · 浏览: 0
标签: SQL 一些 批量 操作 技巧
SQL中的一些批量操作技巧
最近在忙基础数据的导入工作，测试的时候经常需要清空 数据库，MS做的很友好，集成了很多批量处理的方法，大概说一下常用的：
触发器的启用和禁用： 
[sql] 
--禁用：  
ALTER TABLE trig_example DISABLE TRIGGER trig1  
  
--恢复：  
ALTER TABLE trig_example ENABLE TRIGGER trig1  

--禁用某个表上的所有触发器  
ALTER TABLE 你的表 DISABLE TRIGGER all  
  
--启用某个表上的所有触发器  
ALTER TABLE 你的表 enable TRIGGER all  
  
--禁用所有表上的所有触发器  
exec sp_msforeachtable 'ALTER TABLE   DISABLE TRIGGER all'  
  
--启用所有表上的所有触发器  
exec sp_msforeachtable 'ALTER TABLE   enable TRIGGER all'  

约束的启用和禁用：
[sql] 
--禁用所有约束  
exec sp_msforeachtable ’alter table   nocheck CONSTRAINT all’  
--再启用所有外键约束  
exec sp_msforeachtable ’alter table   check constraint all’  

[sql] 
--获得禁用所有外键约束的语句  
select  'ALTER TABLE ['  + b.name +  '] NOCHECK CONSTRAINT ' +  a.name +';' as  禁用约束     
from  sysobjects  a ,sysobjects  b       
where  a.xtype ='f' and  a.parent_obj = b.id  
  
--获得启用所有外键约束的语句  
select  'ALTER TABLE [' + b.name +  '] CHECK CONSTRAINT ' +  a.name +';' as  启用约束       
from  sysobjects  a ,sysobjects  b       
where  a.xtype ='f' and  a.parent_obj = b.id

一个完整的可以清空任意数据库数据的脚本： 
[sql] 
 --禁止触发器和约束  
 exec sp_msforeachtable 'alter table   nocheck CONSTRAINT all'  
 exec sp_msforeachtable 'ALTER TABLE   DISABLE TRIGGER all'  
   
 SET NoCount ON  
   DECLARE @tableName varchar(512)  
   Declare @SQL varchar(2048)  
   SET @tableName=''  
   WHILE NOT EXISTS  
   (     
   --Find all child tables and those which have no relations  
   SELECT T.table_name   FROM INFORMATION_SCHEMA.TABLES T  
          LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC    ON T.table_name = TC.table_name  
     WHERE ( TC.constraint_Type = 'Foreign Key' OR TC.constraint_Type IS NULL )  
         AND T.table_name NOT IN (  'dtproperties ',  'sysconstraints ',  'syssegments ' )  
         AND Table_type =  'BASE TABLE '  
         AND T.table_name >






 @TableName  
         )  
    Begin  
        SELECT @tableName = min(T.table_name)    FROM INFORMATION_SCHEMA.TABLES T  
        LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC    ON T.table_name=TC.table_name  
           WHERE ( TC.constraint_Type =  'Foreign Key ' OR TC.constraint_Type IS NULL )  
         AND T.table_name NOT IN (  'dtproperties ',  'sysconstraints ',  'syssegments ' )  
         AND Table_type =  'BASE TABLE '  
         AND T.table_name > @TableName  
        -- Truncate the table  
         SET @SQL =  'Truncate table  '+ @TableName   
         print (@SQL)  
         Exec(@SQL)  
     End  
    
   SET @TableName=''  
   WHILE EXISTS  
   (   
   --Find all Parent tables  
     SELECT T.table_name     FROM INFORMATION_SCHEMA.TABLES T  
     LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC     ON T.table_name = TC.table_name  
     WHERE TC.constraint_Type =  'Primary Key '  
     AND T.table_name <>  'dtproperties '  
     AND Table_type= 'BASE TABLE '  
     AND T.table_name > @TableName  
     )  
   Begin  
     SELECT @tableName = min(T.table_name)   FROM INFORMATION_SCHEMA.TABLES T  
          LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC   ON T.table_name=TC.table_name  
     WHERE TC.constraint_Type =  'Primary Key '  
     AND T.table_name <>  'dtproperties '  
     AND Table_type =  'BASE TABLE '  
     AND T.table_name > @TableName  
  --   Delete the table  
      
        SET @SQL =  ' delete from  '+ @TableName   
         print (@SQL)  
         Exec(@SQL)  
     --Reset identity column  
         IF EXISTS ( SELECT *   FROM INFORMATION_SCHEMA.COLUMNS  
             WHERE COLUMNPROPERTY(  
             OBJECT_ID( QUOTENAME(table_schema)+  '.' + QUOTENAME(@tableName) ),  
             column_name, 'IsIdentity '  
             ) = 1  
           )  
  
     DBCC CHECKIDENT(@tableName,RESEED,0)  
   End  
   SET NoCount OFF  
 --启用触发器和约束   
exec sp_msforeachtable 'alter table   check constraint all'  
  
exec sp_msforeachtab