Equivalent for drop table table_name cascade constraints in SQL Server
My question is that in Oracle we can use drop table table_nam开发者_JAVA技巧e cascade constraints
to drop a referenced table object. How can I achieve the same in SQL Server?
As I know there is not one command in MsSql, but you can use INFORMATION_SCHEMA and dynamic SQL.
Something like this:
DECLARE @database nvarchar(50)
DECLARE @table nvarchar(50)
set @database = 'MyDatabase'
set @table = 'MyTable'
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
There is an option to let Management Studio generate a script that alters/drops all constraints on dependent objects.
I found the desciption here SQL Server drop table cascade equivalent
精彩评论