Removing SQL Server Components Using SQL-DMO Collections

An application can use the Remove method of a SQL-DMO collection to delete a referenced Microsoft® SQL Server™ component permanently.

When Remove is invoked, SQL-DMO translates the application action into appropriate Transact-SQL statements. For example, using the Remove method of the Tables collection generates and submits a Transact-SQL DROP TABLE statement. Using the Remove method of the DatabaseRoles collection executes Transact-SQL, calling either the sp_droprole or sp_dropapprole system stored procedures.

Any collection Remove method may be constrained by rules applying to the referenced objects. For example, SQL Server does not delete a table if it is referenced by a FOREIGN KEY constraint defined on another table. Using the Remove method of the Tables collection to drop a table used as a foreign key reference fails, returning an appropriate error to the application.

A collection Remove method requires qualification, identifying the targeted object by name or ordinal position. For example:

oSQLServer.DatabaseRoles.Remove("Northwind_Users")

Or

oServer.Databases("Northwind").Users.Remove(5)

Collections referencing owned, SQL Server database objects allow additional qualification by owner name. For example:

oServer.Databases("Northwind").Tables.Remove("Orders", "anne")

Important  A SQL Server administrative action directed by collection membership modification can be time-consuming and can fail. Applications that allow collection membership change should notify the user through a message or busy pointer, and should provide appropriate error handling.