Script to delete all non-system objects in SQL Server 2008

Question

Does anyone have a script that will delete all non-system tables/procs/views from a database?

I created some views, procs and tables which I need to clean up and doing them individually is too cumbersome.

Answer 1

You could always query your system catalog views and have it generate the necessary DROP statements:

SELECT 'DROP PROCEDURE [' + SCHEMA_NAME(schema_id) + '].[' + pr.NAME +']'
FROM sys.procedures pr
WHERE pr.is_ms_shipped = 0

UNION

SELECT 'DROP VIEW [' + SCHEMA_NAME(schema_id) + '].[' + v.NAME + ']'
FROM sys.views v
WHERE v.is_ms_shipped = 0

UNION

SELECT 'ALTER TABLE [' + SCHEMA_NAME(schema_id) + '].[' + OBJECT_NAME(fk.parent_object_ID) + '] DROP CONSTRAINT ' + fk.name 
FROM sys.foreign_keys fk
WHERE is_ms_shipped = 0

UNION

SELECT 'DROP TABLE [' + SCHEMA_NAME(schema_id) + '].[' + t.NAME + ']'
FROM sys.tables t
WHERE t.is_ms_shipped = 0

This will generate a long list of DROP ..... statements, just copy & paste those into a new SSMS window and execute them.

Answer 2

Wouldn't it be easier to drop/recreate the database?

DROP DATABASE yourdbname
CREATE DATABASE yourdbname