Quick way to remove all data from a database (remove FK, truncate and add FK back)

The following script can be used to generate script for these 3 steps which can be used to remove all data from a database quickly and recover FK inclusing check option.


--
-- the following is intresting but has some limitation
-- 1) can not handle the case when FK has multiple columns
-- 2) can not handle the case when FK refers to unique index

SET NOCOUNT ON
 DECLARE @FkDefination TABLE(
  Id INT PRIMARY KEY IDENTITY(1, 1),
  FKConstraintName VARCHAR(255),
  FKConstraintTableSchema VARCHAR(255),
  FKConstraintTableName VARCHAR(255),
  FKConstraintColumnName VARCHAR(255),
  PKConstraintName VARCHAR(255),
  PKConstraintTableSchema VARCHAR(255),
  PKConstraintTableName VARCHAR(255),
  PKConstraintColumnName VARCHAR(255),
  FkIsEnabled TINYINT   
 )
 --
 -- get FK definitions and property
 INSERT INTO @FkDefination(FKConstraintName, FKConstraintTableSchema, FKConstraintTableName, FKConstraintColumnName, FkIsEnabled)
  (
  SELECT
 kcu.CONSTRAINT_NAME
 ,kcu.TABLE_SCHEMA
 ,kcu.TABLE_NAME
 ,kcu.COLUMN_NAME
 ,1-ObjectProperty(fk.constid,'CnstIsDisabled') as FkIsEnabled
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
     INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
     ON kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
  INNER JOIN sysobjects o on o.name=tc.CONSTRAINT_NAME
  INNER JOIN sysforeignkeys fk
  ON fk.constid=o.id 
   WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY'
  )
--
-- get PK and unique constrains name
 UPDATE @FkDefination
 SET PKConstraintName = UNIQUE_CONSTRAINT_NAME
     FROM @FkDefination tt
    INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
    ON tt.FKConstraintName = rc.CONSTRAINT_NAME
--
-- get reference schema and table name
-- this seems to be working with unique constraint and PK, but not unique index?
 UPDATE @FkDefination
 SET PKConstraintTableSchema  = TABLE_SCHEMA,
   PKConstraintTableName  = TABLE_NAME
  FROM @FkDefination fkd INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
    ON fkd.PKConstraintName = tc.CONSTRAINT_NAME
--
-- get reference column name
 UPDATE @FkDefination
 SET PKConstraintColumnName = COLUMN_NAME
  FROM @FkDefination fkd
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    ON fkd.PKConstraintName = kcu.CONSTRAINT_NAME
--select * from @FkDefination
 -- step 1: drop constraint:
 SELECT
        'ALTER TABLE [' + FKConstraintTableSchema + '].[' + FKConstraintTableName + ']  DROP CONSTRAINT ' + FKConstraintName AS DropFkStatement
     FROM @FkDefination
 -- step 2: truncate tables
 SELECT 'TRUNCATE TABLE '+s.name+'.'+t.name as 'TruncateStatement'
   FROM sys.tables t inner join sys.schemas s
  on t.schema_id=s.schema_id
  WHERE t.type='U'
  ORDER BY s.name, t.name
 -- step 3:
 -- add fk back
 SELECT
        'ALTER TABLE [' + FKConstraintTableSchema + '].[' + FKConstraintTableName + '] WITH '+CASE WHEN FkIsEnabled=1 THEN 'CHECK' ELSE 'NOCHECK' END +' ADD CONSTRAINT ' + FKConstraintName + ' FOREIGN KEY(' + FKConstraintColumnName + ') REFERENCES [' + PKConstraintTableSchema + '].[' + PKConstraintTableName + '](' + PKConstraintColumnName + ')' AS CreateFkStatement
     FROM @FkDefination
  WHERE PKConstraintTableName IS NOT NULL



reference:
http://stackoverflow.com/questions/11639868/temporarily-disable-all-foreign-key-constraints