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

Question

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

I created some views, procs, and tables that I need to clear and make them individually, too cumbersome.

Answer 1

You can always request representations of your system catalog and generate the necessary DROP instructions:

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 create a long list of DROP ..... operators DROP ..... , just copy and paste them into a new SSMS window and execute them.

Answer 2

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

 DROP DATABASE yourdbname CREATE DATABASE yourdbname