Clean up SQL Server's master database
If you have accidentally ran a script and your current database was still set to ‘master’, then you end up with a pretty messed up master database. It doesn’t really hurt, but I like to keep my computer tidy. So I came up with a script that cleans up the mess again.
To determine when the mess was created run the following query:
SELECT * FROM sys.objects
It will show you all the objects and the creation date. If you know the date, then run the following script and make sure you set the date properly (line 2). You might need to run it multiple times (some objects cannot be removed during the first run, because of dependencies). Run it until your master database is clean.
DECLARE @date date
SELECT @date = '2016-08-10' -- Adjust to the date that you started the mess
DECLARE @statement nvarchar(250)
-- Kill all foreign keys
DECLARE ForeignKeyCursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT fk.name, tab.name
FROM sys.objects fk
INNER JOIN sys.objects tab ON fk.parent_object_id = tab.object_id
WHERE fk.TYPE = 'F'
DECLARE @keyName sysname
DECLARE @tableName sysname
OPEN ForeignKeyCursor
FETCH NEXT FROM ForeignKeyCursor INTO @keyName, @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @statement = 'ALTER TABLE [' + @tableName + '] DROP CONSTRAINT [' + @keyName + ']'
EXEC sp_executeSql @statement
FETCH NEXT FROM ForeignKeyCursor INTO @keyName, @tableName
END
CLOSE ForeignKeyCursor
DEALLOCATE ForeignKeyCursor
-- Remove all objects in this database
DECLARE ObjectCursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT name, type FROM sys.objects WHERE create_date >= @date
DECLARE @name sysname
DECLARE @type nvarchar(10)
OPEN ObjectCursor
FETCH NEXT FROM ObjectCursor INTO @name, @type
WHILE @@FETCH_STATUS = 0
BEGIN
SET @statement =
CASE @type
WHEN 'FN' THEN 'DROP FUNCTION [' + @name + ']'
WHEN 'IF' THEN 'DROP FUNCTION [' + @name + ']'
WHEN 'TF' THEN 'DROP FUNCTION [' + @name + ']'
WHEN 'P' THEN 'DROP PROCEDURE [' + @name + ']'
WHEN 'U' THEN 'DROP TABLE [' + @name + ']'
WHEN 'V' THEN 'DROP VIEW [' + @name + ']'
ELSE null
END
IF @statement IS NOT NULL
EXEC sp_executeSql @statement
FETCH NEXT FROM ObjectCursor INTO @name, @type
END
CLOSE ObjectCursor
DEALLOCATE ObjectCursor
-- Remove all user defined types in this database
DECLARE UdtCursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT name FROM sys.Types WHERE is_user_defined = 1
DECLARE @udtName sysname
OPEN UdtCursor
FETCH NEXT FROM UdtCursor INTO @udtName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @statement = 'DROP TYPE [' + @udtName + ']'
EXEC sp_executeSql @statement
FETCH NEXT FROM UdtCursor INTO @udtName
END
CLOSE UdtCursor
DEALLOCATE UdtCursor
Good luck…