In oracle, to drop all tables and constraints you would type something like
DROP TABLE myTable CASCADE CONSTRAINTS PURGE;
and this would completely delete the tables and their dependencies. What's the SQL server equivalent??
In SQL Server Management Studio, go to Options / SQL Server Object Explorer / Scripting, and enable 'Generate script for dependent objects'. Then right click the table, script > drop to > new query window and it will generate it for you. Also works for dropping all objects in a db.
The CASCADE option allows you to remove the table and its dependent objects. The RESTRICT option rejects the removal if there is any object depends on the table. The RESTRICT option is the default if you don't explicitly specify it in the DROP TABLE statement.
DROP TABLE always removes any indexes, rules, triggers, and constraints that exist for the target table. However, to drop a table that is referenced by a view or a foreign-key constraint of another table, CASCADE must be specified.
Use the ON DELETE CASCADE option to specify whether you want rows deleted in a child table when corresponding rows are deleted in the parent table. If you do not specify cascading deletes, the default behavior of the database server prevents you from deleting data in a table if other tables reference it.
In SQL Server Management Studio, go to Options / SQL Server Object Explorer / Scripting, and enable 'Generate script for dependent objects'. Then right click the table, script > drop to > new query window and it will generate it for you.
I don't believe SQL has a similarly elegant solution. You have to drop any related constraints first before you can drop the table.
Fortunately, this is all stored in the information schema and you can access that to get your whack list.
This blog post should be able to get you what you need: http://weblogs.asp.net/jgalloway/archive/2006/04/12/442616.aspx
-- t-sql scriptlet to drop all constraints on a table DECLARE @database nvarchar(50) DECLARE @table nvarchar(50) set @database = 'DatabaseName' set @table = 'TableName' DECLARE @sql nvarchar(255) WHILE EXISTS(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_catalog = @database and table_name = @table) BEGIN select @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + CONSTRAINT_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_catalog = @database and table_name = @table exec sp_executesql @sql END
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With