Delete all data in SQL Server database

As an alternative answer, if you Visual Studio SSDT or possibly Red Gate Sql Compare, you could simply run a schema comparison, script it out, drop the old database (possibly make a backup first in case there would be a reason that you will need that data), and then create a new database with the script created by the comparison tool. While on a very small database this may be more work, on a very large database it will be much quicker to simply drop the database then to deal with the different triggers and constraints that may be on the database.

Save yourself some time/space and use TRUNCATE instead of DELETE when possible, will not bloat up your logfile in cases where you have a huge database.

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 
GO 

EXEC sp_MSForEachTable '
        IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
            DELETE FROM ?
        ELSE
            TRUNCATE TABLE ?'

EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL' 
GO

Combining all of the excellent tips in the existing answers along with a couple more, I've put together this script. It's a bit more comprehensive and should be way more efficient.

Before the delete step the script:

• disables triggers (especially important if you have any on delete triggers)
• disables constraint checks
• disables non-clustered indexes (otherwise they get deleted row by row with the table)

For the delete step it will truncate (way faster) where possible or otherwise delete with a tablock hint (tablock will only help with heap tables and since we shrink the files anyway, it may not add anything here).

After the delete step the script will:

• rebuild/enable all indexes (which are empty anyway)
• enable constraint checks
• enable triggers
• re-seed identity columns
• shrink the database files

Delete all data from a database:

use [your_database]
go

exec sp_MSforeachtable 'alter table ? disable trigger all'
go

exec sp_MSforeachtable 'alter table ? nocheck constraint all'
go

-- Disable enabled nonclustered indices
declare @script nvarchar(max)
declare cr cursor fast_forward read_only for
select 'alter index ' + quotename(i.name) + ' on ' + quotename(schema_name(t.schema_id))+'.'+ quotename(t.name) + ' disable'
from sys.indexes i inner join sys.tables t on i.object_id = t.object_id
where i.type_desc = 'nonclustered' and i.name is not null and i.is_disabled = 0;
open cr
fetch next from cr into @script
while @@fetch_status = 0
begin
    execute sp_executesql @script 
    fetch next from cr into @script
end
close cr
deallocate cr
go

exec sp_MSforeachtable 'set quoted_identifier on; if objectproperty(object_id(''?''), ''TableHasForeignRef'') = 1 delete from ? with (tablock) else truncate table ?'
go

exec sp_MSforeachtable 'set quoted_identifier on; alter index all on ? rebuild';
go

exec sp_MSforeachtable 'alter table ? with check check constraint all'
go

exec sp_MSforeachtable 'alter table ? enable trigger all'
go

-- Re-seed identity columns
exec sp_MSforeachtable 'if objectproperty(object_id(''?''), ''TableHasIdentity'') = 1 dbcc checkident(''?'', reseed, 0)'
go

-- Shrink the database files
declare @db_name nvarchar(200) = db_name()
dbcc shrinkdatabase (@db_name, 0);  
go 

EXEC sys.sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' EXEC sys.sp_msforeachtable 'DELETE FROM ?'

EXEC sp_MSForEachTable 'SET QUOTED_IDENTIFIER ON; DELETE FROM ?'

EXEC sys.sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

Yes, it is possible to delete with a single line of code

SELECT 'TRUNCATE TABLE ' + d.NAME + ';' 
FROM   sys.tables d 
WHERE  type = 'U'