T-SQL: Drop All Objects in a SQL Server Database

By March 15, 2012Uncategorized

Sometimes you want to clear out a database, but you can’t just drop the entire database and create a new one. Recently, a colleague needed to do just that, but couldn’t because the database was hosted elsewhere. Here is a simple script that works to drop all objects in any database I’ve tested it against so far. If your database contains object types that mine don’t, you might need to add to this script using the same patterns established here.

declare @stmt nvarchar(max)
 
-- procedures
select top 1 @stmt = isnull( @stmt + '
', '' ) + 'drop procedure ' + quotename(schema_name(schema_id)) + '.' + quotename(name)
from sys.procedures
 
-- check constraints
select top 1 @stmt = isnull( @stmt + '
', '' ) + 'alter table ' + quotename(schema_name(schema_id)) + '.' + quotename(object_name( parent_object_id )) + ' drop constraint ' + quotename(name)
from sys.check_constraints
 
-- functions
select top 1 @stmt = isnull( @stmt + '
', '' ) + 'drop function ' + quotename(schema_name(schema_id)) + '.' + quotename(name)
from sys.objects
where type in ( 'FN', 'IF', 'TF' )
 
-- views
select top 1 @stmt = isnull( @stmt + '
', '' ) + 'drop view ' + quotename(schema_name(schema_id)) + '.' + quotename(name)
from sys.views
 
-- foreign keys
select top 1 @stmt = isnull( @stmt + '
', '' ) + 'alter table ' + quotename(schema_name(schema_id)) + '.' + quotename(object_name( parent_object_id )) + ' drop constraint ' + quotename(name)
from sys.foreign_keys
 
-- tables
select top 1 @stmt = isnull( @stmt + '
', '' ) + 'drop table ' + quotename(schema_name(schema_id)) + '.' + quotename(name)
from sys.tables
 
-- user defined types
select top 1 @stmt = isnull( @stmt + '
', '' ) + 'drop type ' + quotename(schema_name(schema_id)) + '.' + quotename(name)
from sys.types
where is_user_defined = 1
 
-- schemas
select @stmt = isnull( @stmt + '
', '' ) + 'drop schema ' + quotename(name)
from sys.schemas
where principal_id <> schema_id
 
exec sp_executesql @stmt

The following two tabs change content below.

Adam Anderson

Master Consultant at Falafel Software
Adam Anderson is a Microsoft Certified Solution Developer with over 20 years of experience. He started as a consultant using Delphi to rapidly deliver custom, high-quality database applications in a wide variety of industries. Over the course of his career, he has acquired a wide variety of skills including database design, query and index optimization; Database metaprogramming and databases with dynamic, self-modifying schemas; reporting with Crystal Reports, SQL Reporting Services, and Telerik Reporting; .NET Framework, ASP.NET MVC and Web API; ServiceStack; LINQ and Entity Framework; HTML, CSS, and Javascript; Kendo UI; Regular expressions; and lately, Node.js, Express, Angular, MongoDB, PostgreSQL, Redis, Windows Azure, AWS, and more. Fortunately, Adam likes learning new things and then writing about them!