SQL中的一些批量操作技巧

最近在忙基础数据的导入工作，测试的时候经常需要清空数据库，MS做的很友好，集成了很多批量处理的方法，大概说一下常用的：

触发器的启用和禁用：

--禁用：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'

约束的启用和禁用：

--禁用所有约束exec sp_msforeachtable ’alter table ? nocheck CONSTRAINT all’--再启用所有外键约束exec sp_msforeachtable ’alter table ? check constraint all’

--获得禁用所有外键约束的语句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

一个完整的可以清空任意数据库数据的脚本：

 --禁止触发器和约束 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_msforeachtable 'ALTER TABLE ? enable TRIGGER all'