Foreign keys
Is the purpose of making these records orphans and never using a foreign key again? If so, then the method mentioned earlier about disabling the key is fine, otherwise you can instead delete the records that reference the table you want to delete first (or update to point to a more appropriate record, or NULL if it has meaning this is the case). I seem to be coming to this from a different direction than others, are you sure the foreign key is meaningless and if so why not just delete it? At some point someone wanted to restrict this behavior before just disabling the restrictions, I make sure I understand their purpose and have a good rationale for circumventing those guarantees.
source to share
You need to drop the constraint before you can successfully drop the table it references. SQL Server uses the following syntax:
ALTER TABLE <table_name> DROP FOREIGN KEY <foreignkey_name>
Be aware that there is a constraint on the table that refers to the one you want to drop so that the table is modified.
source to share
DO NOT drop a table with foreign key constraints without considering the impact on foreign key tables. Let me explain the impact of simply dropping a foreign key followed by a table with an example.
Consider two tables - details and order details. There is a foreign key constraint that says the part must exist before it can be placed in the orderdetails table. What is stored in the orderdetail table is the identifier for the part from the parts table, not the name or description of the part. Let's say you dropped a foreign key and then omit the table of parts. Now all the data in the orderdetail table is completely useless because you have no way of knowing which part was ordered. This will include orders that have not yet shipped and orders that the customer can call and ask questions. Also, you now have no way to recreate this data other than to restore a backup (I hope you have).
Next, suppose you want to drop the table and re-create it to make changes to the table. Then reload the information and re-enable the foreign key. In this case, you should use the alter table instead of replacing and recreating, but if you don't, you can specify ID numbers that are not the same as the original ones, and therefore the commands will now refer to the wrong IDs. It can be done safely, but you will need to do it very carefully and with great thought in the consequences.
source to share