This will do it. In your SSMS query editor, set the output to text and run this. Then copy/paste the results and execute them. You may need to add other types (CLR functions for example. See complete list here. Constraints, except for FKs are dropped with the parent table.)
SELECT 'ALTER TABLE [' + schema_name(schema_id) + '].[' +
object_name(parent_object_id) + ']
DROP CONSTRAINT [' + name + '];
GO'
FROM sys.foreign_keys
WHERE is_ms_shipped = 0
UNION ALL
SELECT 'DROP ' +
CASE
WHEN type = 'U' THEN 'TABLE'
WHEN type = 'P' THEN 'PROC'
WHEN type = 'V' THEN 'VIEW'
WHEN type = 'FN' THEN 'FUNCTION'
WHEN type = 'SN' THEN 'SYNONYM'
END
+ ' ['+ schema_name(schema_id) + '].[' +
name + '];
GO'
FROM sys.objects
WHERE is_ms_shipped = 0
AND type in ('U', 'P', 'V', 'FN', 'SN');
Clear Database
Posted - 07/26/2012 : 11:22:16
