The SQL server error 3729 occurs during a DROP schema statement when some object reference the schema.

As a part of our Server Management Services, we help our Customers to fix SQL related errors regularly.

Let us today discuss the possible causes and fixes for this error.

What causes the SQL server error 3729?

As we discussed earlier, the SQL error 3729 occurs while we drop a schema that is referenced to some object. A typical error message looks like:

Let us now look at the steps to fix this error message.

How to fix SQL server error 3729?

The first step to solve this error would be to obtain the details of the reference of the schema. Execution of the query below will help to obtain the details of the reference.

SELECT * FROM sys.objects
WHERE name = 'Locations'
AND schema_id = SCHEMA_ID('abc');

Here abc is the corresponding schema name and Locations is the table name.

Modify the schema

In order to fix this issue, we will change the schema of table “Locations” to remove the reference. Run below command to change the schema of the table “Locations” from abc to some other schema.

USE DBName
GO
ALTER SCHEMA <some other schema> TRANSFER abc.Locations

Here DBName is the database name where user/schema/table exists and abc is the schema name.

Drop the schema

After changing the schema, we can proceed with dropping it after ensuring that the schema is empty.  Schema can be deleted with the set of commands given below:

USE [DBName]
GO
DROP SCHEMA [abc]
GO

We can do this using SQL Server Management Studio as well with the steps given below:

1. Connect to SQL Server Instance in SQL Server Management Studio.
2. Expand the database folder followed by the actual database name in which the user exists or created.
3. Expand the Security folder inside the target database followed by the Schemas folder.
4. Right-click on identified schema and choose the delete option. It will delete your schema.

[Need any further assistance in fixing Cloudflare errors? – We’re available 24*7]

Conclusion

In short, SQL server error 3729 occurs while we drop a schema that is referenced to some object. Today, we saw how our Support Engineers fix this error.