MS SQL: empty data of all tables

Sometimes you need to empty data of all tables in a database, what you should do:

1. Backup your DB, and run the following command to see disabled triggers:
SELECT
    sysobjects.name AS trigger_name
    ,USER_NAME(sysobjects.uid) AS trigger_owner
    ,s.name AS table_schema
    ,OBJECT_NAME(parent_obj) AS table_name
    ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate
    ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete
    ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert
    ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter
    ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof
    ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled]
FROM sysobjects
INNER JOIN sysusers
    ON sysobjects.uid = sysusers.uid
INNER JOIN sys.tables t
    ON sysobjects.parent_obj = t.object_id
INNER JOIN sys.schemas s
    ON t.schema_id = s.schema_id
WHERE sysobjects.type = 'TR' and OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') = 1
order by table_name

2. Create a store procedure, e.g. emptyAllTables with code as the following:

CREATE PROCEDURE emplyAllTables
AS

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 ? ENABLE TRIGGER ALL'
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

GO

In the case of you want to skips some important tables and reset identity of table, you can use the following SP:


CREATE PROCEDURE emplyAllTables (@ResetIdentity bit)
AS

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'


DECLARE @SkipTables nvarchar(max)
DECLARE @TableName varchar(255)
DECLARE @SQL varchar(255)
SET @SkipTables = 'table1,table2,table3'

DECLARE curTable CURSOR LOCAL FOR
SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES WHERE CHARINDEX(Table_Name,@SkipTables) = 0 AND TABLE_TYPE='BASE TABLE'

OPEN curTable
FETCH Next FROM curTable INTO @TableName
WHILE @@FETCH_STATUS=0
BEGIN
SET @SQL = 'DELETE FROM ' + @TableName

IF @ResetIdentity = 1 AND OBJECTPROPERTY (OBJECT_ID(@TableName),'TableHasIdentity') = 1
SET @SQL = @SQL + '; DBCC CHECKIDENT(''' + @TableName + ''',RESEED,0)'

EXECUTE(@SQL)
--PRINT @SQL

FETCH Next FROM curTable INTO @TableName
END


EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

GO

3. Manually disable triggers in step 1

Now you have an empty DB for your new start up :)