删除了表的所有约束、视图以及表

我相信经常有同学想删除某一个表时，遇到这样或那样的约束无法删除一头雾水，这时您请不要着急，先看看以下提供的删除所有约束、表、视图等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;