sides of march (Brian DeMarzo)

A few weeks ago, I wrote a post about deleting all tables and constraints in a database. It’s time to take that one step further!

The following SQL code will delete all stored procedures, user-defined functions (UDFs), views, table constraints, and tables (in that order) from a given SQL database. As with the original, you can specify an object name previx by changing the set @tblname = '' as needed.

This is helpful in two situations:

• When you want to delete all objects in a database without dropping/recreating it, in which case you’d specify '%' as the @tblname.
• When you want to delete all objects in a database whose name starts with a certain sequence of characters. For example, if you want to delete all the ASP.Net membership objects in a database, you can specify '%aspnet_%' as the @tblname, since its SQL objects start with aspnet_ or vw_aspnet_.

Note that you should be very careful any time you use scripts like this, as data loss can happen where you least expect it. I use this script for clearing out development databases, but would never use it for production databases.

Anyway, on to the script.

declare @sql varchar(max)
declare @tbl varchar(255)
declare @tblname varchar(255)

--dpecify the "like" clause for your tables here
--to match all tables, use '%'
set @tblname = '%'

--drop stored procedures
select name
into #procs
from sys.objects
where type = 'P'
and name like @tblname

while (select count(*) from #procs) > 0
begin
    select top 1 @tbl = name from #procs
    set @sql = 'drop procedure ' + @tbl
    exec (@sql)
    delete from #procs where name = @tbl
end

drop table #procs

--drop UDFs
select name
into #funcs
from sys.objects
where type in( 'TF', 'FN' )
and name like @tblname

while (select count(*) from #funcs) > 0
begin
    select top 1 @tbl = name from #funcs
    set @sql = 'drop function ' + @tbl
    exec (@sql)
    delete from #funcs where name = @tbl
end

drop table #funcs

--drop views
select name
into #views
from sys.objects
where type = 'V'
and name like @tblname

while (select count(*) from #views) > 0
begin
    select top 1 @tbl = name from #views
    set @sql = 'drop view ' + @tbl
    exec (@sql)
    delete from #views where name = @tbl
end

drop table #views

--drop constraints
select 'alter table ' + table_schema + '.' + table_name + ' drop constraint ' + constraint_name as query
into #constraints
from information_schema.table_constraints
where constraint_type = 'foreign key'
and table_name like @tblname

while (select count(*) from #constraints) > 0
begin
    select top 1 @sql = query from #constraints
    exec (@sql)
    delete from #constraints where query = @sql
end

drop table #constraints

--drop tables
select name
into #tables
from sys.objects
where type = 'U'
and name like @tblname

while (select count(*) from #tables) > 0
begin
    select top 1 @tbl = name from #tables
    set @sql = 'drop table ' + @tbl
    exec (@sql)
    delete from #tables where name = @tbl
end

drop table #tables

go