Removes non-system referential constraints and tables from your master db

masterdb-cleaner.sql

USE [master];
GO

DECLARE @sql NVARCHAR(MAX);

------ Views ------
DECLARE @view_name NVARCHAR(1024);

SELECT  TOP 1
        @view_name = N'[' + [s].[name] + N'].[' + [v].[name] + N']'
FROM    [sys].[views] AS [v]
        JOIN [sys].[schemas] AS [s]
          ON [s].[schema_id] = [v].[schema_id];

WHILE @view_name IS NOT NULL
  BEGIN
    SET @sql = N'DROP VIEW ' + @view_name + N';';

    EXEC [sys].[sp_executesql] @stmt = @sql;

    SELECT  TOP 1
            @view_name = N'[' + [s].[name] + N'].[' + [v].[name] + N']'
    FROM    [sys].[views] AS [v]
            JOIN [sys].[schemas] AS [s]
              ON [s].[schema_id] = [v].[schema_id];
  END;

-------- Constraints ------
DECLARE @schema_name NVARCHAR(1024);
DECLARE @table_name NVARCHAR(1024);
DECLARE @constraint_name NVARCHAR(1024);

SELECT  TOP 1
        @schema_name = [TABLE_SCHEMA]
      , @table_name = [TABLE_NAME]
      , @constraint_name = [CONSTRAINT_NAME]
FROM    [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS]
WHERE   [TABLE_CATALOG] = 'master'
        AND [CONSTRAINT_TYPE] = 'foreign key';

WHILE @table_name IS NOT NULL
      AND @constraint_name IS NOT NULL
  BEGIN
    SET @sql = N'ALTER TABLE [' + @schema_name + N'].[' + @table_name + N'] DROP CONSTRAINT [' + @constraint_name + N'];';

    EXECUTE [sys].[sp_executesql] @stmt = @sql;

    PRINT @sql;

    SELECT  TOP 1
            @schema_name = [TABLE_SCHEMA]
          , @table_name = [TABLE_NAME]
          , @constraint_name = [CONSTRAINT_NAME]
    FROM    [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS]
    WHERE   [TABLE_CATALOG] = 'master'
            AND [CONSTRAINT_TYPE] = 'foreign key';
  END;

------ Tables ------
SELECT  TOP 1
        @schema_name = SCHEMA_NAME([t].[schema_id])
      , @table_name = [t].[name]
FROM    [sys].[tables] AS [t]
WHERE   [t].[type] = 'U'
        AND [t].[is_ms_shipped] = 0;

WHILE @table_name IS NOT NULL
      AND @schema_name IS NOT NULL
  BEGIN
    SET @sql = N'DROP TABLE [' + @schema_name + N'].[' + @table_name + N'];';

    EXECUTE [sys].[sp_executesql] @stmt = @sql;

    SELECT  TOP 1
            @schema_name = SCHEMA_NAME([t].[schema_id])
          , @table_name = [t].[name]
    FROM    [sys].[tables] AS [t]
    WHERE   [t].[type] = 'U'
            AND [t].[is_ms_shipped] = 0;
  END;