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'