How to drop all objects since a particular date
If you haven’t accidentally created objects in master then you can’t be developing on SQL. Invariably this happens when you have a DB creation script that has a USE <DB> at the start. If the DB doesn’t exist then you get an error but SSMS just carries on in the existing database.
This means you end up with lots of objects in the wrong database.
This script generates the drop statements based on the object type. It doesn’t actually drop them you have to copy the relevant statements and then run them in a new window. I don’t like automatically dropping objects automatically so this is a nice compromise. The query returns the date as well so you can select the objects you want to drop.
select case o.type_desc
when 'USER_TABLE' then ' DROP TABLE ' + schema_name(o.schema_id) + '.' + quotename(o.name,'[')
when 'DEFAULT_CONSTRAINT' then 'DROP DEFAULT ' + schema_name(o.schema_id) + '.' + quotename(o.name,'[')
when 'PRIMARY_KEY_CONSTRAINT' then 'ALTER TABLE ' + schema_name(p.schema_id) + '.' + p.name + ' DROP CONSTRAINT ' + quotename( o.name,'[')
when 'UNIQUE_CONSTRAINT' then 'ALTER TABLE ' + schema_name(p.schema_id) + '.' + p.name + ' DROP CONSTRAINT ' + quotename(o.name,'[')
when 'SQL_STORED_PROCEDURE' then 'DROP PROCEDURE ' + schema_name(o.schema_id) + '.' + quotename(o.name,'[')
when 'VIEW' then 'DROP VIEW ' + schema_name(o.schema_id) + '.' + quotename(o.name,'[')
end
, o.create_date
from sys.objects O
left join sys.objects P on O.Parent_object_id =P.object_id
order by o.create_date desc