DECLARE @TruncateStatement nvarchar(2000)
DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
FOR SELECT N'TRUNCATE TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' + QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN TruncateStatements
FETCH NEXT FROM TruncateStatements INTO @TruncateStatement
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM TruncateStatements INTO @TruncateStatement
EXEC(@TruncateStatement)
END
-- Clean up work
CLOSE TruncateStatements
DEALLOCATE TruncateStatements
Since this script doesn't take parent-child relationships into consideration it may have to be run more than once if you have any set up.
Again, I like to give credit where credit is due, but I don't have an original source for this code. Searching on keywords in it will reveal several web pages with it posted.