-- Generates Drop table Commands for All Tables
SELECT 'DROP TABLE ' + table_name
FROM information_schema.TABLES
WHERE table_type = 'BASE TABLE'
-- Loops for each table in the database
exec sp_msforeachtable 'print ''?'''
exec sp_msforeachtable 'truncate table ?'
-- Delete All Constrainsts of Table
DECLARE @database nvarchar(50)
DECLARE @table nvarchar(50)
set @database = 'DNNDB'
set @table = 'DNN_Forum_Threads'
DECLARE @sql nvarchar(255)
WHILE EXISTS(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_catalog = @database and table_name = @table)
BEGIN
select @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + CONSTRAINT_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where constraint_catalog = @database and
table_name = @table
exec sp_executesql @sql
END
-- Delete all tables using CURSOR
DECLARE tables_cursor CURSOR
FOR SELECT name FROM sysobjects WHERE type = ‘U’
OPEN tables_cursor
DECLARE @tablename sysname
FETCH NEXT FROM tables_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
EXEC (’DROP TABLE ‘ + @tablename)
FETCH NEXT FROM tables_cursor INTO @tablename
END
DEALLOCATE tables_cursor
