删除了表的所有约束、视图以及表
我相信经常有同学想删除某一个表时，遇到这样或那样的约束无法删除一头雾水，这时您请不要着急，先看看以下提供的删除所有约束、表、视图等sql脚本，或在测试环境试用。 但是您也可以仅删除某一个对象（表）的所有约束或全部约束，您只需要把游标里用到的SELECT查询语句单独拿出来执行一下，自己看看就明白了，删除了表的所有约束、视图之后就可以删除表了。
1 --删除所有外键约束 2 DECLARE @SQL VARCHAR(99) 3 DECLARE CUR_CONSTRAINT CURSOR LOCAL FOR 4 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 5 + Object_name(parent_object_id) 6 + ' DROP CONSTRAINT ' 7 + Object_name(object_id) 8 FROM sys.objects AS O 9 JOIN sys.schemas AS S 10 ON O.schema_id = S.schema_id 11 WHERE O.type IN( 'C', 'D', 'F' ); 12 13 OPEN CUR_CONSTRAINT; 14 FETCH CUR_CONSTRAINT INTO @SQL; 15 WHILE @@FETCH_STATUS = 0 16 BEGIN 17 EXEC(@SQL); 18 FETCH CUR_CONSTRAINT INTO @SQL; 19 END; 20 21 CLOSE CUR_CONSTRAINT; 22 DEALLOCATE CUR_CONSTRAINT; 23 24 --删除所有视图（存储过程、函数等用同样的方法） 25 DECLARE @SQL VARCHAR(99); 26 DECLARE CUR_VIEW CURSOR LOCAL FOR 27 SELECT 'IF OBJECT_ID(''' + CASE WHEN O.schema_id IS NOT NULL THEN (SELECT NAME+'.' FROM sys.schemas WHERE schema_id = O.schema_id) ELSE '' END 28 + Object_name(object_id) + ''') IS NOT NULL'+ 29 + ' DROP VIEW ' + CASE WHEN O.schema_id IS NOT NULL THEN (SELECT NAME+'.' FROM sys.schemas WHERE schema_id = O.schema_id) ELSE '' END + Object_name(object_id) 30 FROM sys.objects AS O 31 JOIN sys.schemas AS S 32 ON O.schema_id = S.schema_id 33 WHERE O.type IN( 'V' ); 34 35 OPEN CUR_VIEW; 36 FETCH CUR_VIEW INTO @SQL; 37 WHILE @@FETCH_STATUS = 0 38 BEGIN 39 EXEC(@SQL); 40 FETCH CUR_VIEW INTO @SQL; 41 END; 42 CLOSE CUR_VIEW; 43 DEALLOCATE CUR_VIEW; 44 45 -- 删除所有表 46 DECLARE @SQL VARCHAR(99); 47 DECLARE CUR_TABLE CURSOR LOCAL FOR 48 SELECT 'DROP TABLE ' + CASE WHEN O.schema_id IS NOT NULL THEN (SELECT NAME+'.' FROM sys.schemas WHERE schema_id = O.schema_id) ELSE '' END + O.name 49 FROM sys.objects AS O 50 JOIN sys.schemas AS S 51 ON O.schema_id = S.schema_id 52 WHERE O.type = 'U'; 53 54 OPEN CUR_TABLE; 55 FETCH CUR_TABLE INTO @SQL; 56 57 WHILE @@FETCH_STATUS = 0 58 BEGIN 59 EXEC(@SQL); 60 FETCH CUR_TABLE INTO @SQL; 61 END 62 CLOSE CUR_TABLE; 63 DEALLOCATE CUR_TABLE;
