Roman's Weekly SQL Server Tip - How to protect tables from being deleted
SQL Server doesn't have any built-in mechanism that would allow you to protect a table from being accidentally deleted. Things like this unfortunately happen - somebody manually deletes the wrong table, a script gets executed against a different database, there is a bug in SQL code, etc...
If you have a situation where you need to protect one or more tables against accidental deletion, you can exploit the schema-binding functionality in SQL Server 2000. Schema-binding is mainly used for creating indexed views. If an object (table or column) has any schema-bound dependencies, it cannot be modified unless the dependency is removed first. You can create a dummy view referencing the table you want to protect and use the SCHEMABINDING option:
CREATE VIEW MyView
WITH SCHEMABINDING
AS
SELECT COUNT(*) AS Count FROM dbo.MyTable
GO
After you create this view, you cannot delete MyTable as long as MyView exists in the database or it isn't modified without the SCHEMABINDING option.