Cleaning up the SQL Server master database

By: Aaron Bertrand   |   Updated: 2016-10-27   |   Comments (4)   |   Related: More > System Databases

Problem

I'm sure we've all done this: inadvertently created objects in the master database. We may not have noticed we were in master, we may have left out a USE directive in our deployment script, or we may have been placed in that context without our knowledge (as in this Connect bug). At the end of the day, though, you're probably going to want to remove those user-created objects from the system database.

Solution

Without going into things like ensuring you haven't also inadvertently placed external dependencies on any of these objects (like an application that now talks to the master database instead of a user database, or even another database that depends on entities you placed in master), there is a specific strategy and order of operations you should follow when cleaning up system databases. I'm going to divide into two categories (things we won't be dropping and things we will be dropping) the types of things you might have inadvertently created in the master database:

Things we won't be dropping today

There are certain object types I'm not going to handle, either because you're simply not going to be able to drop them, or because I do not want to encourage dropping things that could be irreversible or leave your system in an undesirable state. Some of these, also, may be created in master on purpose:

There are also some system objects that *look* like user objects that we're going to want to avoid touching. While system objects shouldn't show up in sys.objects, a few do, and the list is version-dependent. This is the set I see in current builds of SQL Server 2016:

SELECT [name], [type], [type_desc]
  FROM sys.objects 
  WHERE [type] NOT IN ('IT','S') 
  AND is_ms_shipped = 1
  ORDER BY [name];

/*
  EventNotificationErrorsQueue   SQ   SERVICE_QUEUE
  MSreplication_options          U    USER_TABLE
  QueryNotificationErrorsQueue   SQ   SERVICE_QUEUE
  ServiceBrokerQueue             SQ   SERVICE_QUEUE
  sp_MScleanupmergepublisher     P    SQL_STORED_PROCEDURE
  sp_MSrepl_startup              P    SQL_STORED_PROCEDURE
  spt_fallback_db                U    USER_TABLE
  spt_fallback_dev               U    USER_TABLE
  spt_fallback_usg               U    USER_TABLE
  spt_monitor                    U    USER_TABLE
  spt_values                     V    VIEW
*/

You may also have created your own modules and marked them as system objects, in which case they might show up in the above list too. Note though that there really is no reliable way to distinguish such things from true system stored procedures, so you will have to have some other subjective way to identify those that shouldn't be there, or shouldn't be marked as system objects.

Some of the queries against catalog views may reference columns that only exist in SQL Server 2008 and above. I haven't tested the scripts against 2005, so it is possible you may need to make tweaks to use them against older, unsupported versions. I certainly use syntax that requires 2008+ (combined declare/set and +=).

And finally, there are some other scenarios where you may have adjusted master without meaning to, but that I'm going to shy away from here. Without making an extremely thorough list, I'm avoiding these scenarios either because they're one-off things (like the wrong compatibility level or recovery model), or because they are far too sensitive to allow for automated adjustment (such as cases where you added extra data files, or added a CLR assembly and associated aggregates and functions). I do not want to encourage any magic scripts or shortcuts to "fix" any of these miscues.

Things we will be dropping today

For the items we do want to consider dropping, there is definitely an important order of operations that will produce the least amount of headache. Foreign keys and views should be eliminated before any tables, for example, and partition functions can't be removed until any tables that use them are dropped. Also, in many cases, circular references can exist, so scripts may need to be executed multiple times until they succeed in full.

There are some object types that are simply dropped with the table they're in, so there's no need to worry about them explicitly. You may see these items in sys.objects, but they will be gone once their tables are dropped. Note that there are two kinds of "default" that will register as type = 'D' in sys.objects; a default constraint associated directly with exactly one column, and the old (deprecated) CREATE DEFAULT, defined at the database level, that could be used against multiple columns. We'll deal with the latter later.

In the meantime, these are the objects I don't need to worry about:

The rest are, in order, and acknowledging that this list might not be 100% exhaustive:

The basic approach

Since there are many ways to have circular references, and since it can be complicated to determine the correct order to drop objects, I like to generate scripts that are repeatable - they try to drop objects, indicate if any fail, and can be run again and again until they succeed (which may require further intervention).

For example, if I have two views, one referencing the other with SCHEMABINDING, it should be possible to drop both of those views. If I guess the correct order (or write a script that determines the correct order), I can do this by issuing this pseudo-code:

try { drop view 1; go; drop view 2; }

If the views are listed in the wrong order, then running the same script again should succeed (well, one view would fail because it doesn't exist, but not because of the SCHEMABINDING). If there is a chain of three or four or more views then the script will need to be executed more times to succeed, but unless there are other permission issues it will eventually have eliminated all of the views. All of the scripts below look amazingly similar - build a statement with dynamic SQL from some set of catalog views, wrap it in TRY/CATCH, and output the error message for any entity that fails.

The other thing I like to do is print out or select the SQL generated, so that I can review it and make sure I'm not making some change I don't really want to make. For shorter scripts it makes sense to ensure SSMS has been set to Results to Text, since Results to Grid eliminates carriage returns and line feeds, making the scripts harder to read and validate.

Even in Results to Text, the output can be truncated if you have a large number of objects to drop, and depending on your output settings in SSMS, it may not be possible to view the entire script at once. I've written about ways to get around this in another tip, "Validate the contents of large dynamic SQL strings in SQL Server." You may want to take the valid portion of the current output, execute that manually, and then when you run the original script again, it should output the next chunk of objects that haven't been dropped yet. Hey, I never said this would be simple; I'm just trying to make it a little bit easier. :-)

Let's get cleaning!

Please, before you start, please, please, please: Take a backup of master. You may even want to restore it as a user database so that, in the event you delete some entity that you really need to keep, you'll be able to recover it quickly. This may not work for all entities that aren't stored at the database level; for example, logins, which are stored in sys.server_principals, will pull from the current master, not the copy. But it should work for most other things, like users, partition-related entities, and user objects like tables, stored procedures, sequences, and user-defined functions.

Foreign keys

There are the first set of objects that I drop, simply because it is easy to do, and it has to be done before any of the underlying tables can be dropped. I have written more elaborate scripts around this before, but in this case it is simpler because I am not concerned about re-creating them. Again, we could go to great lengths to determine how to drop them all in the right order, but there still might be a circular reference to deal with, so why bother? Just run the output over and over again until no errors are returned.

-- script to drop Foreign Keys
USE [master];
GO
SET NOCOUNT ON;
DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY
  ALTER TABLE ' + objectname + N'
    DROP CONSTRAINT ' + fkname + N';
END TRY
BEGIN CATCH
  SELECT N''FK ' + fkname + N' failed. Run the script again.'', 
    ERROR_MESSAGE();
END CATCH
' FROM 
(
  SELECT fkname = QUOTENAME(fk.[name]), 
    objectname = QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name])
  FROM sys.foreign_keys AS fk
  INNER JOIN sys.objects AS t
  ON fk.parent_object_id = t.[object_id]
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
) AS src;

SELECT @sql;
--EXEC sys.sp_executesql @sql;

Note: Some of these scripts will not work 100% correctly if you have entity names containing single quotes or square brackets. Don't do that.

Views and functions

Views can reference functions, functions can reference views, views can reference other views, and functions can reference other functions; to add to the fun, all of these can include circular references. As with foreign keys, rather than try to produce a script that will reliably drop all such objects in proper dependency order (which might be impossible), I generate scripts that can be executed multiple times until they generate no output and reveal no errors. If you get the same output after repeated execution, there is probably a clue in the error message about why a certain object can't be removed.

-- script to drop views
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY
  DROP VIEW ' + objectname + N';
END TRY
BEGIN CATCH
  SELECT N''View ' + objectname + N' failed - run the script again.'', 
    ERROR_MESSAGE(); 
END CATCH
' FROM 
(
  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(v.[name])
  FROM sys.views AS v
  INNER JOIN sys.schemas AS s 
  ON v.[schema_id] = s.[schema_id]
  WHERE v.is_ms_shipped = 0
) AS src(objectname);

SELECT @sql;
--EXEC sys.sp_executesql @sql;

-- script to drop functions
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY
  EXEC sys.sp_executesql N''DROP FUNCTION ' + objectname + N';'';
END TRY
BEGIN CATCH
  SELECT N''Function ' + objectname + N' failed - run the script again.'',
    ERROR_MESSAGE(); 
END CATCH
' FROM 
(
  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])
  FROM sys.objects AS o
  INNER JOIN sys.schemas AS s 
  ON o.[schema_id] = s.[schema_id]
  WHERE o.is_ms_shipped = 0
  AND o.[type] IN ('FN','IF','TF')
) AS src(objectname);

SELECT @sql;
--EXEC sys.sp_executesql @sql;

Stored Procedures

The script to generate DROP statements for procedures looks awfully similar to the one for dropping functions. Since stored procedures can reference each other, it is still useful to use TRY/CATCH and re-run the script if any individual statements fail. Please double- and even triple-check this list to make sure that you are only dropping procedures you really want to drop, or that you have copies of them tucked away somewhere.

-- script to drop procedures
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY
  EXEC sys.sp_executesql N''DROP PROCEDURE ' + objectname + N';'';
END TRY
BEGIN CATCH
  SELECT N''Procedure ' + objectname + N' failed - run the script again.'',
    ERROR_MESSAGE();
END CATCH
' FROM
(
  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])
  FROM sys.objects AS o
  INNER JOIN sys.schemas AS s 
  ON o.[schema_id] = s.[schema_id]
  WHERE o.[type] = 'P'
  AND o.is_ms_shipped = 0
) AS src(objectname);

SELECT @sql;
--EXEC sys.sp_executesql @sql;

Tables

Now we can remove any user tables inadvertently created in master. As with procedures, please triple-check this list and make sure you know these should all be removed or that you have backups somewhere just in case.

-- script to drop user tables
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY
  EXEC sys.sp_executesql N''DROP TABLE ' + objectname + N';'';
END TRY
BEGIN CATCH
  SELECT N''Table ' + objectname + N' failed - run the script again.'',
    ERROR_MESSAGE();
END CATCH
' FROM
(
  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])
  FROM sys.tables AS o
  INNER JOIN sys.schemas AS s 
  ON o.[schema_id] = s.[schema_id]
  WHERE o.is_ms_shipped = 0
) AS src(objectname);

SELECT @sql;
--EXEC sys.sp_executesql @sql;

Table Types

Table types are found in sys.types with is_table_type = 1, therefore generating DROP scripts for these is relatively straightforward (and must be done before alias types, since a table type could have a column defined with an alias type):

-- script to drop table types
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY
  EXEC sys.sp_executesql N''DROP TYPE ' + objectname + N';'';
END TRY
BEGIN CATCH
  SELECT N''Type ' + objectname + N' failed - run the script again.'',
    ERROR_MESSAGE();
END CATCH
' FROM
(
  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])
  FROM sys.types AS o
  INNER JOIN sys.schemas AS s 
  ON o.[schema_id] = s.[schema_id]
  WHERE o.is_table_type = 1
) AS src(objectname);

SELECT @sql;
--EXEC sys.sp_executesql @sql;

Alias types

Once tables, modules, and table types have been removed, we should be able to remove alias types, since there should be nothing left with a hard dependency on them. Note that alias types need to be removed before any rules bound to them can be dropped.

-- script to drop alias types
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY
  EXEC sys.sp_executesql N''DROP TYPE ' + objectname + N';'';
END TRY
BEGIN CATCH
  SELECT N''Type ' + objectname + N' failed - run the script again.'',
    ERROR_MESSAGE();
END CATCH
' FROM
(
  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])
  FROM sys.types AS o
  INNER JOIN sys.schemas AS s 
  ON o.[schema_id] = s.[schema_id]
  WHERE o.user_type_id > 256
  AND o.is_table_type = 0
  AND o.is_assembly_type = 0  -- not a CLR UDT
) AS src(objectname);

SELECT @sql;
--EXEC sys.sp_executesql @sql;

Sequences

Next, we can move onto sequences, since there should no longer be any tables or modules that reference them:

-- script to drop sequences
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY
  EXEC sys.sp_executesql N''DROP SEQUENCE ' + objectname + N';'';
END TRY
BEGIN CATCH
  SELECT N''Sequence ' + objectname + N' failed - run the script again.'',
    ERROR_MESSAGE();
END CATCH
' FROM
(
  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])
  FROM sys.sequences AS o
  INNER JOIN sys.schemas AS s 
  ON o.[schema_id] = s.[schema_id]
  WHERE o.is_ms_shipped = 0
) AS src(objectname);

SELECT @sql;
--EXEC sys.sp_executesql @sql;

Old-style CREATE RULE

Back in early versions of SQL Server, there was a standalone type of check constraint called a rule. You could associate a Rule with an alias type or directly to a column using sp_bindrule. Once all alias types and columns have been dropped, it should be safe to drop all rules.

-- script to drop old-style Rules
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY
  EXEC sys.sp_executesql N''DROP RULE ' + objectname + N';'';
END TRY
BEGIN CATCH
  SELECT N''Rule ' + objectname + N' failed - run the script again.'',
    ERROR_MESSAGE();
END CATCH
' FROM
(
  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])
  FROM sys.objects AS o
  INNER JOIN sys.schemas AS s 
  ON o.[schema_id] = s.[schema_id]
  WHERE o.is_ms_shipped = 0
  AND [type] = 'R'
  AND parent_object_id = 0
) AS src(objectname);

SELECT @sql;
--EXEC sys.sp_executesql @sql;

Old-style CREATE DEFAULT

Quite similar to old-style rules, it should now be safe to drop old-style defaults, even if they had been bound to alias types or columns using sp_bindefault.

-- script to drop old-style Defaults
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY
  EXEC sys.sp_executesql N''DROP DEFAULT ' + objectname + N';'';
END TRY
BEGIN CATCH
  SELECT N''Default ' + objectname + N' failed - run the script again.'',
    ERROR_MESSAGE();
END CATCH
' FROM
(
  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])
  FROM sys.objects AS o
  INNER JOIN sys.schemas AS s 
  ON o.[schema_id] = s.[schema_id]
  WHERE o.is_ms_shipped = 0
  AND [type] = 'D'
  AND parent_object_id = 0
) AS src(objectname);

SELECT @sql;
--EXEC sys.sp_executesql @sql;

Partition functions

With no user tables left, it should be safe to eliminate partition schemes and functions. We should drop functions first since that is the only way we'll be able to drop the partition schemes (unless a scheme was created and left unused).

-- script to drop partition functions
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY
  EXEC sys.sp_executesql N''DROP PARITITON FUNCTION ' + objectname + N';'';
END TRY
BEGIN CATCH
  SELECT N''Partition function ' + objectname + N' failed - run the script again.'',
    ERROR_MESSAGE();
END CATCH
' FROM
(
  SELECT QUOTENAME([name]) 
  FROM sys.partition_functions
) AS src(objectname);

SELECT @sql;
--EXEC sys.sp_executesql @sql;

Partition schemes

And now with partition functions out of the way, we can safely drop any partition schemes. As I alluded to before, I'm not going to help build out scripts that will drop partition-related filegroups, because I think that's a little too dangerous.

-- script to drop partition schemes
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY
  EXEC sys.sp_executesql N''DROP PARITITON SCHEME ' + objectname + N';'';
END TRY
BEGIN CATCH
  SELECT N''Partition scheme ' + objectname + N' failed - run the script again.'',
    ERROR_MESSAGE();
END CATCH
' FROM
(
  SELECT QUOTENAME([name]) 
  FROM sys.partition_schemes
) AS src(objectname);

SELECT @sql;
--EXEC sys.sp_executesql @sql;

Synonyms

As mentioned above, synonyms should be relatively painless to remove, but as with any other entity, there's no way for a script running against the current SQL Server instance to possibly know of any external code referencing these synonyms. So please be sure these are actually safe to drop.

-- script to drop synonyms
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY
  EXEC sys.sp_executesql N''DROP SYNONYM ' + objectname + N';'';
END TRY
BEGIN CATCH
  SELECT N''Synonym ' + objectname + N' failed - run the script again.'',
    ERROR_MESSAGE();
END CATCH
' FROM
(
  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])
  FROM sys.synonyms AS o
  INNER JOIN sys.schemas AS s 
  ON o.[schema_id] = s.[schema_id]
) AS src(objectname);

SELECT @sql;
--EXEC sys.sp_executesql @sql;

Schemas

At this point, it should be safe to drop any user-defined schemas you have inadvertently created, as long as you have dropped (or transferred) all objects they owned. These may fail, of course, if a schema owns any of the entity types we haven't touched, like assembly_types or service_queues.

-- script to drop schemas
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY
  EXEC sys.sp_executesql N''DROP SCHEMA ' + objectname + N';'';
END TRY
BEGIN CATCH
  SELECT N''Schema ' + objectname + N' failed - run the script again.'',
    ERROR_MESSAGE();
END CATCH
' FROM
(
  SELECT QUOTENAME(s.[name])
  FROM sys.schemas AS s 
  WHERE [schema_id] BETWEEN 5 AND 16383
) AS src(objectname);

SELECT @sql;
--EXEC sys.sp_executesql @sql;

For any schemas that no longer exist, you should update the users in the event any of them have specified those schemas as default. The dropped schema will still be reflected in sys.database_principals, and will lead to unexpected errors if the user tries to create an object without explicitly naming the schema.

-- script to re-route default schemas
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY
  ALTER USER ' + QUOTENAME(name) + N' WITH DEFAULT_SCHEMA = dbo;
 END TRY
 BEGIN CATCH
  SELECT N''User ' + p.name + N' failed - run the script again.'',
     ERROR_MESSAGE();
 END CATCH
'
FROM sys.database_principals AS p 
WHERE p.default_schema_name IS NOT NULL
AND NOT EXISTS 
(
  SELECT 1 
  FROM sys.schemas AS s 
  WHERE name = p.default_schema_name
);
 
SELECT @sql;
--EXEC sys.sp_executesql @sql;

Roles and Users

This is where chicken and egg can be the worst, because users can be role members and role owners, and roles can be role owners and members too, and users and roles can own schemas you've kept. So you need to first transfer any user-owned roles to dbo (or any other user/role you'll keep), then transfer authorization of the schemas, then drop all of the members of all the roles, then drop the roles, and then finally drop the users.

-- script to change ownership of roles to dbo
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY 
  ALTER AUTHORIZATION ON ROLE::' + QUOTENAME(r.name) + N' TO dbo;
 END TRY
 BEGIN CATCH
   SELECT N''Role ' + r.name + N' failed - run the script again.'',
     ERROR_MESSAGE();
 END CATCH
'
FROM sys.database_principals AS r
INNER JOIN sys.database_principals AS u
ON r.owning_principal_id = u.principal_id
WHERE r.[type] = 'R'
AND u.name NOT IN (N'public', N'dbo', N'guest', N'INFORMATION_SCHEMA', N'sys')
AND u.name NOT LIKE N'##%##' -- hopefully you don't name users/roles this way!
AND r.is_fixed_role = 0;

SELECT @sql;
--EXEC sys.sp_executesql @sql;

-- script to change ownership of schemas to dbo
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY
  ALTER AUTHORIZATION ON SCHEMA::' + QUOTENAME(name) + N' TO dbo;
 END TRY
 BEGIN CATCH
  SELECT N''Schema ' + name + N' failed - run the script again.'',
    ERROR_MESSAGE(); END CATCH
'
FROM sys.schemas 
WHERE [schema_id] BETWEEN 5 AND 16383
AND principal_id BETWEEN 5 AND 16383;

SELECT @sql;
--EXEC sys.sp_executesql @sql;

There's an added wrinkle that in SQL Server 2012 you should use ALTER ROLE to remove members, but in earlier versions you need to use sys.sp_droprolemember.

-- script to remove members from roles
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY
' + CASE WHEN CONVERT(tinyint,
  PARSENAME(CONVERT(nvarchar(128),
  SERVERPROPERTY(N'ProductVersion')),4)) >= 11 -- 2012+
 THEN
  N'ALTER ROLE ' + QUOTENAME(r.name) + N' DROP MEMBER ' 
   + QUOTENAME(m.name) + N';'
 ELSE
  N'EXEC [sys].[sp_droprolemember] @rolename = N''' + r.name 
    + ''', @membername = N''' + m.name + N''';'
 END + N'
 END TRY
 BEGIN CATCH
   SELECT N''Role ' + r.name + N' failed - run the script again.'',
     ERROR_MESSAGE();
 END CATCH
'
FROM sys.database_role_members AS rm
INNER JOIN sys.database_principals AS r
ON rm.role_principal_id = r.principal_id
AND r.is_fixed_role = 0
INNER JOIN sys.database_principals AS m
ON rm.member_principal_id = m.principal_id;

SELECT @sql;
--EXEC sys.sp_executesql @sql;

-- script to drop roles
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY
  DROP ROLE ' + QUOTENAME(r.name) + N';
 END TRY
 BEGIN CATCH
  SELECT N''Role ' + r.name + N' failed - run the script again.'',
     ERROR_MESSAGE();
 END CATCH
'
FROM sys.database_principals AS r
WHERE r.[type] = 'R'
AND r.name <> N'public' 
AND r.is_fixed_role = 0;

SELECT @sql;
--EXEC sys.sp_executesql @sql;

And finally, we can drop the users:

-- script to drop users
USE [master];
GO
DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY
  DROP USER ' + QUOTENAME(u.name) + N';
 END TRY
 BEGIN CATCH
  SELECT N''User ' + u.name + N' failed - run the script again.'',
     ERROR_MESSAGE();
 END CATCH
'
FROM sys.database_principals AS u
WHERE u.[type] IN ('U','S')
AND u.name NOT IN (N'public', N'dbo', N'guest', N'INFORMATION_SCHEMA', N'sys')
AND u.name NOT LIKE N'##%##';

SELECT @sql;
--EXEC sys.sp_executesql @sql;

...and that should pretty much do it.

Summary

Cleaning master can be a very messy and manual affair. While you can automate writing out metadata commands to drop objects and other entities in a "safe" order, nothing can substitute for qualitative analysis - a human should be absolutely certain an entity doesn't belong in master before deleting it. So please don't use this code as a model to blindly let rip on all your servers.

I've seen a lot of systems with polluted master databases, and I've helped clean up quite a few, but can't possibly claim to have seen everything. Did I miss any entity types? Do you have a scenario where my prescribed order doesn't work? Do you think I'm doing anything dangerous here? Let me know in the comments below!

Next Steps

• See these tips and other resources:
  • While I aimed for simplicity, to make some of these metadata queries even simpler, you could probably look at some of the views I demonstrated in an earlier tip, Making SQL Server metadata queries easier with these new Views.
  • If you have issues with a lot of user entities in master, it might be useful to review Validate the contents of large dynamic SQL strings in SQL Server.
  • An earlier tip showed a technique you can use to script out CREATE statements before deleting objects, so that they can be re-generated if dropped inadvertently: Drop and Re-Create All Foreign Key Constraints in SQL Server. This is more challenging with complex entities like tables than for simpler objects like constraints, but the principles are the same.
  • The official documentation on catalog views can be quite helpful when determining the predicates you should use when generating scripts that should include or exclude certain types of objects. Those mentioned above:
    • sys.database_principals
    • sys.database_role_members
    • sys.foreign_keys
    • sys.objects
    • sys.partition_functions
    • sys.partition_schemes
    • sys.procedures
    • sys.schemas
    • sys.sequences
    • sys.server_principals
    • sys.synonyms
    • sys.tables
    • sys.types
    • sys.views

About the author

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

View all my tips