Allen White

Cleaning up Empty Schemas after Upgrading to SQL Server 2005

One of the problems I've run into after upgrading from SQL Server 2000 to SQL Server 2005 has been the "empty" schemas the upgrade process creates. After the upgrade, in each database, I've found a schema for every defined user and every defined database role, even the system-defined roles! To clean these up I've created a console application using SMO.

First, I define a class to hold the names of the schemas which qualify to be dropped.

Public Class dbSchema
    Public name As String
    Sub New(ByVal newName As String)
        name = newName
    End Sub
End Class

Next, in the Main subroutine, I defined the objects I'll need to do the cleanup. The first variable is an array of type Urn, which is what SMO returns from the EnumOwnedObjects method of the Schema object. It's this method that makes this code work so nicely.

        Dim arrURN() As Urn
        Dim colSchema As SchemaCollection
        Dim objSchema As Schema
        Dim colDatabase As DatabaseCollection
        Dim objDatabase As Database
        Dim colDropSchema As New Collection
        Dim strSchemaName As String
        Dim objDropSchema As dbSchema

Next we have to connect to our target server.

        'Connect to the server
        Dim srv As Server
        Dim srvConn As ServerConnection
        srv = New Server("MyServer")
        srvConn = srv.ConnectionContext
        srvConn.LoginSecure = True

Now comes the fun part. I loop through the database collection on the server, and then the schema collection within each database. You have to leave the 'sys', 'guest', 'dbo' and 'INFORMATION_SCHEMA' schemas in place, because SQL Server gets upset if you try to remove them, so after making sure we're not looking at one of those we call the EnumOwnedObjects method for the schema. If the Length property of the resultant array is 0, then the schema doesn't own any objects and can be safely removed, so I add it to the collection of schema names to be dropped. Once I loop through the list of schemas, I loop through the list of delete candidates, and drop each one.

        colDatabase = srv.Databases                 'Get the server's database collection
        For Each objDatabase In colDatabase
            colSchema = objDatabase.Schemas         'Get the Schema collection
            colDropSchema.Clear()                   'Init the list of empty schema names
            For Each objSchema In colSchema
                ' Leave the 'sys', 'guest', 'dbo' and 'INFORMATION_SCHEMA' schemas in place
                If Not (objSchema.Name = "sys" Or objSchema.Name = "guest" _
                      Or objSchema.Name = "dbo" Or objSchema.Name = "INFORMATION_SCHEMA") Then

                    arrURN = objSchema.EnumOwnedObjects 'Determine if the schema owns any objects
                    If arrURN.Length = 0 Then           'If not, then add the schema name to the drop list
                        strSchemaName = objSchema.Name
                        If Not (colDropSchema.Contains(strSchemaName)) Then
                            colDropSchema.Add(New dbSchema(strSchemaName), strSchemaName)
                        End If
                    End If
                End If
            Next
            For Each objDropSchema In colDropSchema             'Loop through the drop list
                objSchema = colSchema.Item(objDropSchema.name)  'Attach the schema to be dropped
                objSchema.Drop()                                'Drop the schema
            Next
        Next

Remember that this code must be run against SQL Server 2005 databases only, so you might want to test the compatibility level to be equal to 90 before going forward, but on my target servers all the databases have been upgraded.

Allen