Most SQL users experience an SQL Server error 3729 when they want to "DROP" action on a table of a database.
Recently, we have seen rising cases of SQL related issues which our Server Experts have provided support in solving them.
In this context, we will look into why this error occurs and how best to fix it.
What triggers SQL server error 3729?
Basically, SQL error 3729 takes place when a schema referenced to some object in the database is undergoing a drop action.
Below you will see how to fix this error.
How to Solve SQL Server error 3729
From our experience, the easiest way to fix this error is to get more information about the reference of the affected schema. To do this, run the following query command;
SELECT * FROM sys.objects
WHERE name = 'Name_of_Table'
AND schema_id = SCHEMA_ID('xyz');
Here "xyz" refers to the name of the schema while "Name_of_Table" is the affected table.
Modify the Schema of the table
Changing the schema of the table "Name_of_Table" to take off the reference will solve this error. In this case, You should use the command below to modify the schema of the table "Name_of_Table" from xyz to some other schema;
ALTER SCHEMA <some other schema> TRANSFER xyz.Name_of_Table
Here Name_of_DB is the name of the database where user/schema/table is located and xyz is the name of the Schema.
Drop Schema of the table
As soon as you have modified the schema, then drop it once you are sure that the schema is empty. Next, delete the schema with the command below;
DROP SCHEMA [xyz]
Alternatively, this can be done locally via SQL Server Management Studio by following the steps provided below;
i. Connect to the SQL Server Database in your SQL Server Management Studio.
ii. Then, Navigate to the database directory which is the concerned database where the user exists.
iii. Next Open the Security folder in the database.
iv. Finally, do a right-click on the concerned schema and select the delete option to delete the schema.