Sql Azure , Truncate and Reseed All Tables

Question

DBCC is not available in Sql Azure so basically we cant perform DBCC actions. So how we do the reset for identity in Sql Azure.

i written this to Truncate all records and Reseed the Tables to 1 and obviously this is not going to work cause DBCC is not Allowed.

EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
EXEC sp_MSForEachTable ‘DELETE FROM ?’
EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
DBCC checkident (?, RESEED, 1)  ??
GO

So how i do the Reseed with this script.

Answer 1

Here is what I did:

declare @dropConstraintsSql nvarchar(max);
declare @enableConstraintsSql nvarchar(max);
declare @deleteSql nvarchar(max);

-- create a string that contains all sql statements to drop contstraints 
-- the tables are selected by matching their schema_id and type ('U' is table)   

SELECT @dropConstraintsSql = COALESCE(@dropConstraintsSql + ';','') + 'ALTER TABLE [' + name + '] NOCHECK CONSTRAINT all'
FROM sys.all_objects 
WHERE type='U' and schema_id=1
-- AND... other conditions to match your tables 


-- create a string that contains all your DELETE statements...

SELECT @deleteSql = COALESCE(@deleteSql + ';','') + 'DELETE FROM [' + name + '] WHERE ...'
FROM sys.all_objects 
WHERE type='U' and schema_id=1
-- AND ... other conditions to match your tables 

-- create a string that contains all sql statements to reenable contstraints    

SELECT @enableConstraintsSql = COALESCE(@enableConstraintsSql + ';','') + 'ALTER TABLE [' + name + '] WITH CHECK CHECK CONSTRAINT all'
FROM sys.all_objects 
WHERE type='U' and schema_id=1
-- AND ... other conditions to match your tables 

-- in order to check if the sqls are correct you can ...
-- print @dropConstraintsSql
-- print @deleteSql
-- print @enableConstraintsSql


-- execute the sql statements in a transaction

begin tran 

exec sp_executesql  @dropConstraintsSql
exec sp_executesql  @deleteSql
exec sp_executesql  @enableConstraintsSql

-- commit if everything is fine
rollback