我现在想删除一个schema下的所有表

[jan8826]

我现在想删除一个schema下的所有表，但是表上有约束，所以写了删除这个schema下的约束的语句，但是总是报错，所以请教，报错为消息102，级别15，状态1，第1行，中间的sql语句我试过了，是没有问题的。请各位指点，数据库版本是sql server2008
--删除schema为0408下所有表
DECLARE @TableName sysname, @msg varchar(100), @cmd varchar(100)

DECLARE table_cur CURSOR FOR

select sys.schemas.name+'.'+sys.objects.name from sys.objects,sys.schemas
where sys.objects.type='U' and sys.objects.schema_id=sys.schemas.schema_id and sys.schemas.name='0408'

OPEN table_cur
FETCH NEXT FROM table_cur INTO @TableName

WHILE @@fetch_status = 0
BEGIN
IF @@fetch_status = -2
CONTINUE
set @msg='drop table '+ @TableName
exec(@msg)
FETCH NEXT FROM table_cur INTO @TableName
END
DEALLOCATE table_cur
GO


--删除schema是0408下的所有表的约束
DECLARE @SQL VARCHAR(99)
DECLARE CUR_CONSTRAINT CURSOR LOCAL FOR
SELECT
'ALTER TABLE '+
CASE WHEN O.schema_id IS NOT NULL THEN (SELECT NAME+'.' FROM sys.schemas WHERE schema_id = O.schema_id) ELSE '' END
+OBJECT_NAME(parent_object_id)+' DROP CONSTRAINT '+OBJECT_NAME(object_id)
FROM sys.objects AS O JOIN sys.schemas AS S on O.schema_id=S.schema_id and O.schema_id = S.schema_id and S.name='0408'
WHERE O.type IN('C','D','F')
OPEN CUR_CONSTRAINT
FETCH CUR_CONSTRAINT INTO @SQL
WHILE @@FETCH_STATUS =0
BEGIN
EXEC(@SQL)
FETCH CUR_CONSTRAINT INTO @SQL
END
CLOSE CUR_CONSTRAINT
DEALLOCATE CUR_CONSTRAINT

[阳泉酒家小当家]

约束这个也不是那么容易就删除的，A依赖于B，B依赖于C，那么要先删除A的约束，再删除B的，否则还是会报错。 比较好的办法是，直接用truncate table，然后再用delete from ，最后，drop table