A developer’s view

Clean up SQL Server's master database

· Read in about 2 min · (422 Words)
SQL Server master

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…

Comments