Delete multiple objects from a SQL Server database

Question

Is there a way to delete multiple object inside database? I have 20 tables, 40 views, 40 stored procedures, 20 triggers.

I don't want to delete them one by one. I've been naming all the objects starting with _, e.g. _TableName, _ViewName, _StoredProcedureName, etc.

Thank you.

Szymon · Accepted Answer · 2014-08-25 03:41:53Z

1

You can achieve that using dynamic SQL and query catalog views for your objects (there's one for tables, views, procedures, etc.). You can then build up a list of drop statement separated by ; and execute it.

For example, for dropping tables it will be:

declare @sql nvarchar(max) = N''

select @sql = @sql + N'drop table ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N'; ' 
from sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
where left(t.name, 1) = '_'

exec sp_executesql @sql

edited Aug 25, 2014 at 3:41

answered Aug 25, 2014 at 3:20

Szymon

43k16 gold badges99 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Haminteu Over a year ago

I tried your code from new query windows then execute it.. It doesn't work. My table wasn't drop.

Szymon Over a year ago

@Haminteu Sorry, when copying the code I missed the line that actually executes it. Please try now.

Haminteu Over a year ago

sys.tables.. Its for table, how about view ? Storeprocedure ?

Szymon Over a year ago

Check this link: msdn.microsoft.com/en-us/library/ms189783%28v=sql.100%29.aspx. There's a view for views, procedures, triggers, etc.

Collectives™ on Stack Overflow

Delete multiple objects from a SQL Server database

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related