Skip to content

Instantly share code, notes, and snippets.

@gavincampbell gavincampbell/oops.sql
Last active Mar 6, 2016

Embed
What would you like to do?
Transact-SQL script to drop all user-defined objects accidentally created in the SQL Server master database
DECLARE @dropStatements TABLE (dropStatement varchar(1000), dropOrder tinyint);
DECLARE @dropStatement varchar(1000);
INSERT INTO @dropStatements
select
CONCAT(
'DROP',' ',
typename,' ',
OBJECT_SCHEMA_NAME(object_id),
'.',
OBJECT_NAME(object_id),';'),
dropOrder
from sys.objects so
CROSS APPLY( VALUES
('PC', 'PROCEDURE', 7)
,('P', 'PROCEDURE', 6)
,('U', 'TABLE', 4)
,('IF', 'FUNCTION', 3)
,('FN', 'FUNCTION', 2)
,('V', 'VIEW', 1)
) typenames (typeid, typename, dropOrder)
WHERE
so.type = typenames.typeid and
so.is_ms_shipped = 0
union all
select CONCAT('DROP SCHEMA', ' ', name,';'), 7 from sys.schemas where schema_id <> principal_id
union all
select CONCAT('DROP TYPE' ,' ',SCHEMA_NAME(schema_id),'.', name, ';') , 6 from sys.types where is_user_defined = 1;
declare c cursor for select dropStatement from @dropStatements order by dropOrder;
open c;
FETCH NEXT FROM c into @dropStatement;
while @@FETCH_STATUS =0
BEGIN
exec (@dropStatement);
FETCH NEXT FROM c into @dropStatement;
END
close c;
deallocate c;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.