Dropping a table with all its dependencies (Microsoft SQL Server)

Question

How can I drop a table with all its dependencies [SPs, Views, etc.] (Microsoft SQL Server) without knowing its dependencies upfront? I know I can display all dependencies in Mangement Studio but I'm searching for utility script that I could simply speficy an object and it would drop this object with all its dependencies.

Answer 1

The best thing to do it is "Generate scripts for Drop"

Select Database -> Right Click -> Tasks -> Generate Scripts - will open wizard for generating scripts

• Select the database -> next
• Set option 'Script to create' to true (want to create)
• Set option 'Script to Drop' to true (want to drop)
• Set option 'Generate script for dependent object' to true -> Next
• Select the Check box to select objects wish to create script
• Select the choice to write script (File, New window, Clipboard)

Execute the script

This way we can customize our script i.e., we can do scripting for selected objects of a database.

I hope this will help you!

Best Wishes, JP

Answer 2

You can use Sp_Depends to find the dependencies. With that you can modify the script from this answer Maybe someone less lazy than me will do that for you.

Note: Each object of course could have its own dependencies so you'll need to process them as well.